#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. B5-9438/39 B7-9558/659 B14-9564 (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; 03-30-2019 at 06:41 PM. Reason: Uploaded new file. |
#15
|
|||
|
|||
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
COUNTIF with multiple criteria | Marcia | Excel | 5 | 02-17-2019 08:16 AM |
Use countif across multiple tabs for a column | marconexcel | Excel | 6 | 02-21-2017 05:38 AM |
Sumif with multiple sum range | Haha88 | Excel | 6 | 10-29-2015 03:26 AM |
multiple criteria and within a range? | hhppyy | Excel | 1 | 07-17-2015 08:17 AM |
Using range object to work with multiple columns | kjworduser | Word VBA | 1 | 11-01-2013 03:03 AM |