# 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.

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

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

4. ## Re: Sum if multiple criteria

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

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"}))}``

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1