SUMPRODUCT Formula, but need items to fall in same category

Originally Posted by AL1976

hi,
is this file ( http://wikisend.com/download/302488/Sum based on 2 criteria new.xlsx ) enough to satisfy your need ?
it won't work if you can have Gas1, Gas2 etc , and it may not work if you add new categories with long names beginning like some others.
AL

As you know the idea of a forum is to solve poster's problems and share the solution with other members. Adding a link to an external file is potentially unsafe and may keep members form getting the solution ( due to admin settings at work or whatever reason).

So, if you could post the solution on the forum, everybody will gain from it - Thank you

You can try this

=SUMPRODUCT(--($B$2:$B$16>=$G$2), --($B$2:$B$16<=$G$3), --(ISNUMBER(SEARCH(F5,$C$2:$C$16))), $D$2:$D$16)

Or this one

=SUMPRODUCT(($B$2:$B$16>=$G$2)*($B$2:$B$16<=$G$3)*(LEFT($C$2:$C$16,LEN(F5))=$F5), $D$2:$D$16)



Also, there is word Electric is misspelled in column C.

Another way is by using this array formula, so it needs to be confirmed with Ctrl+Shift+Enter

G5 =SUMPRODUCT(--($B$2:$B$16>=$G$2), --($B$2:$B$16<=$G$3), --(IF(NOT(ISERROR(SEARCH(F5,$C$2:$C$16)>0)),$C$2:$C$16<>"")), $D$2:$D$16)

and then drag down.

With all respect, isn't this overkill? What does --(IF(NOT(ISERROR(SEARCH(F5,$C$2:$C$16)>0)),$C$2:$C$16<>"") do more than --(ISNUMBER(SEARCH(F5,$C$2:$C$16)) ?

SUMPRODUCT already works with arrays. Embedding it in an array formula will not speed things up