SUMIF using 3 criterias

[crismat09]

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

 

[FDibbins]

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]

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]

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:

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

Regards,

D

 

[FDibbins]

Drumstick, that is still adding values on the same date, twice

 

[Aladin Akyurek]

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]

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

Happy new year.

Cris

 

[FDibbins]

happy to help

I saw that Aladin gave the FEQU formula I mentioned