# Sum if multiple criteria

1. ## Sum if multiple criteria

IN the worksheet attached, i'm trying to come up with a formula that sum the data in columns A:D if certain conditions are met

In the highlighted yellow cell (H2), i'm trying to SUM anything that has the following criteria:
- Affinity in column A
- "Like itLike it" OR "Love itLove it" in Column B
- Belongs to Test

So for example, the final value in H2 should be 132+126.  Register To Reply

2. ## Re: Sum if multiple criteria

For test:

=SUM(SUMIFS(C2:C89,A2:A89,G2,B2:B89,{"Like itLike it";"Love itLove it"}))

For control:

=SUM(SUMIFS(D2:D89,A2:A89,G2,B2:B89,{"Like itLike it";"Love itLove it"}))  Register To Reply

3. ## Re: Sum if multiple criteria

Thanks! by any chance would this formula change if charts A:D were pivot tables? I'm trying to use the formula in Google Sheets and for some reason it's not summing up Like it AND Love itit's only showing one or the other  Register To Reply

4. ## Re: Sum if multiple criteria

You posted in the wrong section if this is for GoogleSheets - shall I move your thread?  Register To Reply

5. ## Re: Sum if multiple criteria

In response to question #1, the formula is an array. Since there are spaces in the cell text of the attachments C1 and D1, the formula can be copied and pasted down and pasted on the right.
Formula is
HTML Code:
``{=SUM(SUMIFS(OFFSET(\$C:\$C,,MATCH(H\$1,\$C\$1:\$D\$1,)-1),\$A:\$A,\$G2,\$B:\$B,{"Like itLike it";"Love itLove it"}))}``  Register To Reply

6. ## Re: Sum if multiple criteria

H2=IF(\$G2<>"",SUMPRODUCT((ISNUMBER(MATCH(\$B\$2:\$B\$100,\$B\$4:\$B\$5,0)))*(\$C\$2:\$D\$100)*(\$C\$1:\$D\$1=H\$1)*(\$A\$2:\$A\$100=\$G2)),"")

Copy across and down  Register To Reply