sumproduct with multiple strings

[desert rat]

Hi All


trying to use a SUMPRODUCT to to total the amount of times "never" or "quit" appears in a column. Only problem is "quit" is a partial string so it can include "quit >5years" or "quit <5years"


Note - i am using =SUBTOTAL(103,B2) in A2 so when i filter by different sites it only totals/displays those results.


This is what i am using to total the amount of time "never" appears

=SUMPRODUCT(--(F2:F236="never"),SUBTOTAL(103,OFFSET(F2,ROW(F2:F236)-ROW(F2),0)))


this formula works for "never" but i can't workout out how to modify it to inlude the search for "quit"


Any ideas????


Cheers

 

[NARAYANK991]

Hi ,


Have you tried this :


=SUMPRODUCT(--(LEFT(F2:F236,4)="quit"),SUBTOTAL(103,OFFSET(F2,ROW(F2:F236)-ROW(F2),0)))


Narayan

 

[desert rat]

Thanks for that! - it works but it displays ######## in the cell but when i hover over the cell with the mouse it brings up 67 which is correct.


is there a way to get the number to display in the cell.....???


Thansk again

 

[NARAYANK991]

Hi ,


I think it's just a matter of formatting ; click on the left-aligned button ( Alignment dialog ) , and you should see the cell value properly.


Narayan

 

[desert rat]

Thanks that did the trick


Just out of curiosity i don;t suppose there's a way to combine the 2 SUMPRODUCT statements in to 1....??


=SUMPRODUCT(--(F2:F236="never"),SUBTOTAL(103,OFFSET(F2,ROW(F2:F236)-ROW(F2),0)))


and


=SUMPRODUCT(--(LEFT(F2:F236,4)="quit"),SUBTOTAL(103,OFFSET(F2,ROW(F2:F236)-ROW(F2),0)))


Cheers

 

[NARAYANK991]

Hi ,


Since these are mutually exclusive criteria , you can just add the two formulae together in one cell :


=SUMPRODUCT(--(F2:F236="never"),SUBTOTAL(103,OFFSET(F2,ROW(F2:F236)-ROW(F2),0))) +

SUMPRODUCT(--(LEFT(F2:F236,4)="quit"),SUBTOTAL(103,OFFSET(F2,ROW(F2:F236)-ROW(F2),0)))


or


=SUMPRODUCT(--((F2:F236="never")+(LEFT(F2:F236,4)="quit"))*SUBTOTAL(103,OFFSET(F2,ROW(F2:F236)-ROW(F2),0)))


Narayan

 

[anand_babu]

Hi Narayan,


I am new to this forum and to be honest i am very much impressed on the way you guys address the queries. You guys are awesome and the best !!!


Coming to the point, Can you help me on understanding the subtotal part of the formula (SUBTOTAL(103,OFFSET(F2,ROW(F2:F236)-ROW(F2)) ?


Can we use something like this =SUM(COUNTIF(F:F,{"never","quit*"})) ?


Thanks