#1




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




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




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. B59438/39 B79558/659 B149564 (Rest of cells are blank) This would equal 5 
#4




Maybe like the attached.

#5




Or…....................
=SUMPRODUCT(0+(LEN(B5:B14)>0)*(LEN(B5:B14)LEN(SUBSTITUTE(B5:B14,"/",""))+1)) Regards Bosco 
#6




How did you achieve that? I don't see a formula.

#7




Formula is in cell D14.

#8




[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




Only time the sum changes is with 9???

#10




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




Maybe this one is better?

#12




Now the sum changes with 9???/?? And 9???/??? But not with 9??? The opposite of the first formula. Really appreciate the help.

#13




Quote:
=SUMPRODUCT(ISNUMBER(1/(0+LEFT(B5:B14)=9))*(LEN(B5:B14)LEN(SUBSTITUTE(B5:B14,"/",""))+1)) Regards Bosco 
#14




I will try once more.
Last edited by xor; 03302019 at 06:41 PM. Reason: Uploaded new file. 
#15





Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
COUNTIF with multiple criteria  Marcia  Excel  5  02172019 08:16 AM 
Use countif across multiple tabs for a column  marconexcel  Excel  6  02212017 05:38 AM 
Sumif with multiple sum range  Haha88  Excel  6  10292015 03:26 AM 
multiple criteria and within a range?  hhppyy  Excel  1  07172015 08:17 AM 
Using range object to work with multiple columns  kjworduser  Word VBA  1  11012013 03:03 AM 