SUMIF Help

[ddub25]

Struggling to create the correct syntax for a SUMIF. I want to ask:

IF Y3="Dan W"
THEN, SUMIF('Profit Breakdown'!A5:A26,THIS MONTH,'Profit Breakdown'!C5:C26)

IF Y3="Paul P"
THEN, SUMIF('Profit Breakdown'!M5:M26,THIS MONTH,'Profit Breakdown'!O5:O26)

IF NOT=0

Can anyone help, thanks

Dan

 

[MrVillareal]

Hi thank you for your feedback.
I get zero value for my formula. Try it.

Excel Workbook
	A	B	M	N	R	S	T	U
1	Date	Amount	Date	Amount		DateCriteria	Person	Amount
2	Sep-10	100	Sep-10	300		23/11/2011	Dan W	0
3	Oct-10	200	Oct-10	350
4	Nov-10	300	Nov-10	400
5	Dec-10	400	Dec-10	450
6	Jan-11	500	Jan-11	500
7	Feb-11	600	Feb-11	550
8	Mar-11	700	Mar-11	600
9	Apr-11	800	Apr-11	650
10	May-11	900	May-11	700
Sheet3

Excel 2010

Cell Formulas
Range	Formula
U2	=IF(T2="Dan W",SUMIF(A2:A10,S2,B2:B10),IF(T2="Paul P",SUMIF(M2:M10,S2,N2:N10),0))

 

[ddub25]

yes, it does give 0 if I enter 23/11/11 in Y1, but it also seems to give 0 for other date entries where it should return a value. I think that your formula looks for an exact match of the date and if not found it returns 0.

All my months in range A5:A26 are for the 10th of each month so unless i enter the 10th of a particular month in Y1 I get a return of 0. Thats why I was asking if I can use a date format that just specifies Month/Date. I am using "mmm-yy" but it does not let me just enter, say 08/11 to get Aug-11. If I enter 08/11 in Y1 or the range A5:A26 I get Nov-11 displayed in the cell, but 08/11/2011 in the formula bar.

Can I simplify the date format?

Dan

 

[MrVillareal]

How about this.

Excel Workbook
	A	B	M	N	R	S	T	U
1	Date	Amount	Date	Amount		DateCriteria	Person	Amount
2	Sep-10	100	Sep-10	300		02/02/2011	Paul P	550
3	Oct-10	200	Oct-10	350
4	Nov-10	300	Nov-10	400
5	Dec-10	400	Dec-10	450
6	Jan-11	500	Jan-11	500
7	Feb-11	600	Feb-11	550
8	Mar-11	700	Mar-11	600
9	Apr-11	800	Apr-11	650
10	May-11	900	May-11	700
Sheet3

Excel 2010

Cell Formulas
Range	Formula
U2	=IF(T2="Dan W",SUMPRODUCT((MONTH(A2:A10)=MONTH(S2))*(YEAR(A2:A10)=YEAR(S2))*(B2:B10)),IF(T2="Paul P",SUMPRODUCT((MONTH(M2:M10)=MONTH(S2))*(YEAR(M2:M10)=YEAR(S2))*(N2:N10)),0))

 

[scottylad2]

The last posters solution should work

 

[ddub25]

Thanks very much for your help Villareal and Scotty, the last solution works great. Thanks very much for your time. Here's the final adaptation:

=IF(R3="Dan W",SUMPRODUCT((MONTH('Profit Breakdown'!A5:A26)=MONTH(Y1))*(YEAR('Profit Breakdown'!A5:A26)=YEAR(Y1))*('Profit Breakdown'!B5:B26)),IF(R3="Paul P",SUMPRODUCT((MONTH('Profit Breakdown'!M5:M26)=MONTH(Y1))*(YEAR('Profit Breakdown'!M5:M26)=YEAR(Y1))*('Profit Breakdown'!N5:N26)),0))

Dan

 

[MrVillareal]

Welcome! Tnx for feedback