# Merging two formulas

#### uk747

##### Well-known Member
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

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
 Tom abc dog frog 06:30 01:30 Fred dog 06:00 harry abc frog cat 03:00 02:00 mary frog 03:00 abc mary cat 01:00

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

results should be
 with abc dog 06:30 cat 03:00 frog 04:30

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

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))

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

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.

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)

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)))

Thanks Neil

Replies
5
Views
223
Replies
6
Views
360
Replies
6
Views
2K
Replies
6
Views
394
Replies
5
Views
587

1,203,027
Messages
6,053,120
Members
444,640
Latest member
Dramonzo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back