# SUMIF with multiple criteria and first instance only

#### vgresia

##### New Member
Hi there - I'm trying to sum all of the data in column D that have either a yes in the primary or secondary category, but only include the first instance of that value. In the example below, the sum would be 3 (1 for A, 2 for B). Please help!!
 Area Primary Site Secondary Site Value A yes no 1 B yes no 2 A no yes 1 C no no 3

<tbody>
</tbody>

#### Marcelo Branco

##### MrExcel MVP
Welcome to Mr Excel forum

What is the expected result for a scenario like this (observe that only the second A meet the condition)?

 A​ B​ C​ D​ 1​ Area​ Primary Site​ Secondary Site​ Value​ 2​ A​ no​ no​ 1​ 3​ B​ yes​ no​ 2​ 4​ A​ no​ yes​ 5​ 5​ C​ no​ no​ 3​

It would be 7 (2 for B and 5 for A)?

M.

#### vgresia

##### New Member
The expected result would be 7, because row 3 meets the condition (has a yes), row 4 meets the condition (is the first A to have a yes).

#### Eric W

##### MrExcel MVP
The easiest way would be to add a helper column, like this:

ABCDEFG
1AreaPrimary SiteSecondary SiteValue
2Ayesno1133
3Byesno21
4Anoyes70
5Cnono30

</tbody>
Sheet4

Worksheet Formulas
CellFormula
E2=(COUNTIF(B2:C2,"yes")>0)*(COUNTIFS(A\$1:A1,A2,B\$1:B1,"yes")+COUNTIFS(A\$1:A1,A2,C\$1:C1,"yes")=0)
F2=SUMPRODUCT(D2:D5,E2:E5)

</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
G2{=SUM(IF(ROW(A2:A5)-ROW(A2)+1=IF(IFERROR(MATCH(A2:A5&"yes",A2:A5&B2:B5,0),2^20)< IFERROR(MATCH(A2:A5&"yes",A2:A5&C2:C5,0),2^20),IFERROR(MATCH(A2:A5&"yes",A2:A5&B2:B5,0),2^20),IFERROR(MATCH(A2:A5&"yes",A2:A5&C2:C5,0),2^20)),D2:D5))}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The formula in E2 figures out if the area on that row is the first instance of the area with a "yes". Then the very simple formula in F2 gives you the sum.

It is possible to create a single-cell formula to get that sum, for example, look at the G2 array formula. It's pretty complicated, but maybe someone else can improve on it.

Last edited:

#### vgresia

##### New Member
The code that ended up working for this was:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">=SUMPRODUCT((((\$B\$2:\$B\$5="yes")+(\$C\$2:\$C\$5="yes"))*\$D\$2:\$D\$5)/((COUNTIFS(\$A\$2:\$A\$5,\$A\$2:\$A\$5,\$B\$2:\$B\$5,"yes")+COUNTIFS(\$A\$2:\$A\$5,\$A\$2:\$A\$5,\$C\$2:\$C\$5,"yes"))+((\$B\$2:\$B\$5="no")*(\$C\$2:\$C\$5="no"))))</code>

#### Marcelo Branco

##### MrExcel MVP
Another possible formula

 A​ B​ C​ D​ E​ F​ 1​ Area​ Primary Site​ Secondary Site​ Value​ Result​ 2​ A​ no​ no​ 1​ 7​ 3​ B​ yes​ no​ 2​ 4​ A​ no​ yes​ 5​ 5​ C​ no​ no​ 3​ 6​

Array formula in F2
=SUM(IF(FREQUENCY(IFERROR(MATCH(A2:A5,IF((B2:B5="yes")+(C2:C5="yes"),A2:A5),0),FALSE),ROW(A2:A5)-ROW(A2)+1),D2:D5))
confirmed with Ctrl+Shift+Enter, not just Enter

M.