# SUMIF using 3 criterias

#### crismat09

##### New Member
Hi,

I am using SUMIF with 3 criteria to solve a problem. The formula works well 95% of the time. The issue occurs when two criteria repeat. I will try to illustrate:

Source Table:
 Fund Ticker Ex-Div Pay Quantity Dollar AAAA CC 12/20/2016 100 \$1.00 AAAA CC 12/20/2016 100 \$0.50 AAAA DD 12/24/2016 250 \$2.00 AAAA DD 12/24/2016 250 \$0.75 BBBB CC 12/20/2016 50 \$1.00 BBBB DD 12/24/2016 75 \$2.00

<colgroup><col span="2"><col><col><col></colgroup><tbody>
</tbody>

I need to know the number of shares(column D) held by a fund (column A) of a specific security (column B) on a specific date (column C). The formula works well, in this case for fund B because there was only one distribution for each security. The issue occurs on fund AAAA, security CC, which had two distribution on the same date. Therefore, the formula returns 200 shares rather than 100. In summary, I would need to create a formula that returns only the first quantity rather than the sum of all. I have tried index/match, but it did not work as I have three criteria.

Any help is much appreciated.

Cris

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### FDibbins

##### Well-known Member
You could probably do this by building FRQUENCY into the formula (making it an ARRAY formula), but perhaps a simpler way would be to add a helper column and do a running count, then include that as 1 of the criteria...
 L​ M​ N​ O​ P​ Q​ R​ S​ 1​ Fund Ticker Ex-Div Pay Quantity Dollar 2​ AAAA CC 12/20/2016​ 100 \$1.00 1​ 100​ 3​ AAAA CC 12/20/2016​ 100 \$0.50 2​ 4​ AAAA DD 12/24/2016​ 250 \$2.00 1​ 5​ AAAA DD 12/24/2016​ 250 \$0.75 2​ 6​ BBBB CC 12/20/2016​ 50 \$1.00 1​ 7​ BBBB DD 12/24/2016​ 75 \$2.00 1​
Q2=COUNTIFS(\$L\$2:L2,L2,\$M\$2:M2,M2,\$N\$2:N2,N2)
copied down
S2=SUMIFS(\$O\$2:\$O\$7,\$L\$2:\$L\$7,L2,\$M\$2:\$M\$7,M2,\$N\$2:\$N\$7,N2,\$Q\$2:\$Q\$7,1)

#### Tetra201

##### MrExcel MVP
Try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=INDEX(\$D\$2:\$D\$7,MATCH(--"2016-12-20",IF(\$A\$2:\$A\$7="AAAA",IF(\$B\$2:\$B\$7="CC",\$C\$2:\$C\$7)),0))

#### Drumstick22

##### New Member
Hi chrismat09,

I accomplished this using the Sumifs formula and using cells G2, G3 and G4 for the user to input the Fund, Ticker and Ex-Div Pay. Please see below and let me know if this works for you:

Code:
``=SUMIFS(D2:D7,A2:A7,G2,B2:B7,G3,C2:C7,G4)``

Regards,

D

#### FDibbins

##### Well-known Member
Drumstick, that is still adding values on the same date, twice

##### MrExcel MVP
In H1 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF(1-(\$A\$2:\$A\$7=""),IF(\$A\$2:\$A\$7=\$G1,MATCH(\$A\$2:\$A\$7,\$A\$2:\$A\$7,0))),ROW(\$A\$2:\$A\$7)-ROW(\$A\$2)+1),\$D\$2:\$D\$7))

where G1 = AAAA and G2 = BBBB.

#### crismat09

##### New Member
Ford,
Thanks for such a quick and useful response. Appreciate.

Happy new year.

Cris

#### FDibbins

##### Well-known Member
happy to help

I saw that Aladin gave the FEQU formula I mentioned

Replies
4
Views
262
Replies
25
Views
492
Replies
3
Views
662
Replies
3
Views
245
Replies
5
Views
648

1,191,187
Messages
5,985,194
Members
439,947
Latest member
fabiannic

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back