SUMIF with multiple criteria and first instance only

[vgresia]

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]

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]

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]

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

	A	B	C	D	E	F	G
1	Area	Primary Site	Secondary Site	Value
2	A	yes	no	1	1	3	3
3	B	yes	no	2	1
4	A	no	yes	7	0
5	C	no	no	3	0

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula 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) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula 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))} <thead> </thead><tbody> </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.

 

[vgresia]

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]

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.