MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Need to add two sumif, if both are true

Posted by Chief on January 29, 2002 4:02 PM

I have two formulas here and both work by there self, I was wondering is there any way of joining both of them where both are true then it adds the cell !$D$4

=SUMIF('Dispatch Record Sheet 1'!$B$6:$B$57,$H5,'Dispatch Record Sheet 1'!$D$4)
=SUMIF('Dispatch Record Sheet 1'!$C$6:$C$57,$A5,'Dispatch Record Sheet 1'!$D$4)

Posted by Aladin Akyurek on January 29, 2002 4:48 PM

I read your formula as if you want to check if any value in B6:B57 is equal to the value in H5. If so, return the value in D4. As you might know,

SUM(D4) = D4 or SUM(9) = 9 [ SUMIF behaves like SUM ]

Your second SUMIF has a similar reading. If this is also your intent, you can use instead:

=IF(OR(COUNTIF('Dispatch Record Sheet 1'!$B$6:$B$57,$H5),COUNTIF('Dispatch Record Sheet 1'!$C$6:$C$57,$A5)),'Dispatch Record Sheet 1'!$D$4,"")


Posted by Aladin Akyurek on January 29, 2002 4:50 PM

Replace OR with AND in the formula I suggested.