SUMIF with multiple criteria and first instance only

vgresia

New Member
Joined
May 31, 2018
Messages
21
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!! :)
AreaPrimary SiteSecondary SiteValue
Ayesno1
Byesno2
Anoyes1
Cnono3

<tbody>
</tbody>
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,344
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
Joined
May 31, 2018
Messages
21
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
Joined
Aug 18, 2015
Messages
9,159
The easiest way would be to add a helper column, like this:

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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)

<thead>
</thead><tbody>
</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))}

<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.
 
Last edited:

vgresia

New Member
Joined
May 31, 2018
Messages
21
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
Joined
Aug 23, 2010
Messages
16,344
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.
 

Forum statistics

Threads
1,085,542
Messages
5,384,332
Members
401,887
Latest member
Somesh

Some videos you may like

This Week's Hot Topics

Top