Merging two formulas

[uk747]

currently have

=SUMPRODUCT(--(F$22:G$30=$B36),F$23:G$31)
which looks in Range for B36 in the even cells (22, 24 , 26 etc and adds up the values in odd cells 23, 25, 27 etc)

What i need is to add a Sumif =SUMIF(B22:B31,"*ABC*",C22:D31) so it also needs to only add where B22 :B31 contains ABC

thanks

 

[uk747]

Tom abc</SPAN>	dog</SPAN>	frog</SPAN>
	06:30</SPAN>	01:30</SPAN>
Fred</SPAN>	dog</SPAN>
	06:00</SPAN>
harry abc</SPAN>	frog</SPAN>	cat</SPAN>
	03:00</SPAN>	02:00</SPAN>
mary</SPAN>	frog</SPAN>
	03:00</SPAN>
abc mary</SPAN>	cat</SPAN>
	01:00</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL span=2></COLGROUP>

results should be

	with abc</SPAN>
dog</SPAN>	06:30</SPAN>
cat</SPAN>	03:00</SPAN>
frog</SPAN>	04:30</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>

 

[njimack]

SUMPRODUCT usually refers to ranges which are 1 column wide, so I suspect your existing function is returning incorrect results. I think you're after something like this:

=SUMPRODUCT(--(F$22:F$30=$B36),--(ISNUMBER(SEARCH("ABC",B$22:B$30))),(G$23:G$31))

 

[uk747]

thanks

=SUMPRODUCT(--(F$22:G$30=$B36),F$23:G$31) does cover two columns and the results were ok, however the sumif part where it looks in column b is only 1 column B (B22:B31)


I used =SUMPRODUCT(--(C$22:C$30=$B36),--(ISNUMBER(SEARCH("ABC",B$22:B$30))),(C$23:C$31))+SUMPRODUCT(--(D$22:D$30=$B36),--(ISNUMBER(SEARCH("ABC",B$22:B$30))),(D$23:D$31))

Is there a way to combine without having to add 2 sumproducts together

 

[njimack]

If you're summing 2 different ranges (C23:C31 and D23:D31) then I can't see how this can be done in a single formula.

 

[uk747]

its only 1 range C22:D31 which covers 2 columns i just needed to include B22:B31 which some included ABC
=SUMPRODUCT(--(C$22:D$30=$B36),C$23:D$31)

 

[njimack]

Maybe the below array formula (confirm with ctrl+shift+enter):
=SUM(IF(C$22:D$30=$B36,IF(ISNUMBER(SEARCH("ABC",B$22:B$30)),C$23:D$31)))

 

[uk747]

Thanks Neil