Microsoft Office Forums Countif multiple same range
 User Name Remember Me? Password
 Register FAQ Search Today's Posts Mark Forums Read

 Thread Tools Display Modes
#1
03-29-2019, 05:19 PM
 Greggey1 Windows Vista Office 2016 Novice Join Date: Mar 2019 Posts: 10
Countif multiple same range

Hi,
Having an issue figuring out the right formula for the following issue.
Inputting range values into B5:B14
Need 9???/?? To = 2

9???/??? To = 2
9??? To = 1
Feel like the right start of the formula would be =sum countif(B:5:B14,
Have no idea how to finish it or if it’s possible.
#2
03-29-2019, 06:45 PM
 Marcia Windows 7 32bit Office 2007 Competent Performer Join Date: May 2018 Location: Philippines Posts: 202

Are you trying to count the number of occurrences of each data in range B5:B14?, If so, how about this formula in say, C5.

=countif(B\$5:B\$14,B5)
#3
03-29-2019, 07:40 PM
 Greggey1 Windows Vista Office 2016 Novice Join Date: Mar 2019 Posts: 10

Sorry it's hard for me to explain I’m a rookie at this. .
B5:B14 can have three different option in each cell. (9???/??, 9???/???, 9????)
I need the total of B5:B14.

B5-9438/39
B7-9558/659
B14-9564
(Rest of cells are blank)
This would equal 5
#4
03-29-2019, 10:18 PM
 xor Windows 10 Office 2016 Expert Join Date: Oct 2015 Posts: 1,028

Maybe like the attached.
Attached Files
 Multiple_Same.xlsx (10.2 KB, 7 views)
#5
03-30-2019, 02:48 AM
 bosco_yip Windows 10 Office 2016 Novice Join Date: Feb 2019 Location: Singapore Posts: 7

Or…....................

=SUMPRODUCT(0+(LEN(B5:B14)>0)*(LEN(B5:B14)-LEN(SUBSTITUTE(B5:B14,"/",""))+1))

Regards
Bosco
#6
03-30-2019, 04:34 AM
 Greggey1 Windows Vista Office 2016 Novice Join Date: Mar 2019 Posts: 10

Quote:
 Originally Posted by xor Maybe like the attached.
How did you achieve that? I don't see a formula.
#7
03-30-2019, 04:40 AM
 xor Windows 10 Office 2016 Expert Join Date: Oct 2015 Posts: 1,028

Formula is in cell D14.
#8
03-30-2019, 05:14 AM
 Greggey1 Windows Vista Office 2016 Novice Join Date: Mar 2019 Posts: 10

[QUOTE=xor;139958]Formula is in cell D14.[/QUOTE
Was looking at it with my phone. That formula is quite impressive. However I typed 9368/69 in b9 and it didn't change the sum.
#9
03-30-2019, 05:16 AM
 Greggey1 Windows Vista Office 2016 Novice Join Date: Mar 2019 Posts: 10

Only time the sum changes is with 9???
#10
03-30-2019, 05:32 AM
 Greggey1 Windows Vista Office 2016 Novice Join Date: Mar 2019 Posts: 10

Quote:
 Originally Posted by bosco_yip Or….................... =SUMPRODUCT(0+(LEN(B5:B14)>0)*(LEN(B5:B14)-LEN(SUBSTITUTE(B5:B14,"/",""))+1)) Regards Bosco
That's very close. The only thing is if someone who enters a number not beginning with 9 I don't want it to count. Thanks for the help.
#11
03-30-2019, 07:17 AM
 xor Windows 10 Office 2016 Expert Join Date: Oct 2015 Posts: 1,028

Maybe this one is better?
Attached Files
 Multiple_Same_2.xlsx (10.2 KB, 2 views)
#12
03-30-2019, 07:29 AM
 Greggey1 Windows Vista Office 2016 Novice Join Date: Mar 2019 Posts: 10

Now the sum changes with 9???/?? And 9???/??? But not with 9??? The opposite of the first formula. Really appreciate the help.
#13
03-30-2019, 08:44 AM
 bosco_yip Windows 10 Office 2016 Novice Join Date: Feb 2019 Location: Singapore Posts: 7

Quote:
 Originally Posted by Greggey1 That's very close. The only thing is if someone who enters a number not beginning with 9 I don't want it to count. Thanks for the help.
Then, the formula changed to

=SUMPRODUCT(ISNUMBER(1/(0+LEFT(B5:B14)=9))*(LEN(B5:B14)-LEN(SUBSTITUTE(B5:B14,"/",""))+1))

Regards
Bosco
#14
03-30-2019, 09:36 AM
 xor Windows 10 Office 2016 Expert Join Date: Oct 2015 Posts: 1,028

I will try once more.
Attached Files
 Multiple_Same_4.xlsx (11.0 KB, 3 views)

Last edited by xor; 03-30-2019 at 06:41 PM. Reason: Uploaded new file.
#15
03-30-2019, 11:07 AM
 Greggey1 Windows Vista Office 2016 Novice Join Date: Mar 2019 Posts: 10

Quote:
 Originally Posted by xor I will try once more.
You are amazing. .....Thanks!!!

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post Marcia Excel 5 02-17-2019 08:16 AM marconexcel Excel 6 02-21-2017 05:38 AM Haha88 Excel 6 10-29-2015 03:26 AM hhppyy Excel 1 07-17-2015 08:17 AM kjworduser Word VBA 1 11-01-2013 03:03 AM

All times are GMT -7. The time now is 09:57 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top