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

 

[Derek Brown]

Is "THIS MONTH" a named formula? If so, there should not be a space.
What is the result/error that you are getting?

 

[ddub25]

I've developed the formula a bit further and arrived at the formula below so far.

=IF(R3="Dan W",SUMIFS('Profit Breakdown'!B5:B26,'Profit Breakdown'!A5:A26,"10/8/2011"),IF(R3="Paul P",SUMIFS('Profit Breakdown'!N5:N26,'Profit Breakdown'!M5:M26,"10/8/2011"),0))

The bit that I am struggling with is the ["10/8/2011"] part. The range A5:A26 contains the months of 2011 in mmm-yy format. Instead of having "10/8/2011", I want to replace this
part with a formula that looks for the row that contains this month in A5:A26 and returns the corresponding value in B5:B26.

["THIS MONTH" was not a named formula, just text trying to explain what I was after. Hope it's explained better above.] Thanks.

Dan

 

[scottylad2]

Excel Workbook
	D	E	F	G
13	Dan	Month Selection
14		16/08/2011
15	20/06/2011	278	281	97632
16	25/07/2011	341	283
17	07/11/2011	285	346
18	26/10/2011	257	360
19	10/09/2011	268	296
20	11/08/2011	339	288
21	31/10/2011	309	293
22	01/10/2011	353	304
23	06/12/2011	282	354
24	01/10/2011	291	282
Sheet4

not entirley sure of your query, but this should adapt

 

[ddub25]

Thanks, I've adapted your formula to this:

=IF(R3="Dan W",SUMPRODUCT(--(MONTH(Y1)=MONTH('Profit Breakdown'!A5:A26)),'Profit Breakdown'!B5:B26),"")

How would I also be able to include another IF in the formula? I'd like to also lump this formula:

=IF(R3="Paul P",SUMPRODUCT(--(MONTH(Y1)=MONTH('Profit Breakdown'!M5:M26)),'Profit Breakdown'!N5:N26),"")

with the original:

=IF(R3="Dan W",SUMPRODUCT(--(MONTH(Y1)=MONTH('Profit Breakdown'!A5:A26)),'Profit Breakdown'!B5:B26),"")

Thanks.

Dan

 

[MrVillareal]

Hi

Try this simple illustration. Is this related to your problem? Kinda a different presentation but I dont know if thats what you want.

By changing the value in R2 you get result to the other table for Dan W or Paul P.

Excel Workbook
	A	B	M	N	R	S	T	U	V
1	Date	Amount	Date	Amount	Person		DateCriteria	Dan W	Paul P
2	Aug-11	100	Aug-11	300	Paul P		10/08/2011		3350
3	Aug-11	200	Aug-11	350
4	Aug-11	300	Aug-11	400
5	Aug-11	400	Aug-11	450
6	Sep-11	500	Sep-11	500
7	Aug-11	600	Aug-11	550
8	Aug-11	700	Aug-11	600
9	Oct-11	800	Oct-11	650
10	Aug-11	900	Aug-11	700
Sheet3

Excel 2010

Cell Formulas
Range	Formula
U2	=IF(R2=U1,SUMIF(A2:A10,T2,B2:B10),"")
V2	=IF(R2=V1,SUMIF(M2:M10,T2,N2:N10),"")

 

[ddub25]

I just want to join the two formulas below together to make one formula. How would I do that?

=IF(R3="Dan W",SUMPRODUCT(--(MONTH(Y1)=MONTH('Profit Breakdown'!A5:A26)),'Profit Breakdown'!B5:B26),"")

=IF(R3="Paul P",SUMPRODUCT(--(MONTH(Y1)=MONTH('Profit Breakdown'!M5:M26)),'Profit Breakdown'!N5:N26),"")

Dan

 

[MrVillareal]

How about this.

Excel Workbook
	A	B	M	N	R	S	T	U
1	Date	Amount	Date	Amount		DateCriteria	Person	Amount
2	Aug-11	100	Aug-11	300		10/08/2011	Paul P	3350
3	Aug-11	200	Aug-11	350
4	Aug-11	300	Aug-11	400
5	Aug-11	400	Aug-11	450
6	Sep-11	500	Sep-11	500
7	Aug-11	600	Aug-11	550
8	Aug-11	700	Aug-11	600
9	Oct-11	800	Oct-11	650
10	Aug-11	900	Aug-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))

 

[scottylad2]

I just want to join the two formulas below together to make one formula. How would I do that?

=IF(R3="Dan W",SUMPRODUCT(--(MONTH(Y1)=MONTH('Profit Breakdown'!A5:A26)),'Profit Breakdown'!B5:B26),"")

=IF(R3="Paul P",SUMPRODUCT(--(MONTH(Y1)=MONTH('Profit Breakdown'!M5:M26)),'Profit Breakdown'!N5:N26),"")

Dan

At the end of the firs formula, instead of the double "" put the entire second formula, which means if the first name isnt found then the search wilk take place on the second. Put the entire second formula but dont inckude the aditional =

Cant to an example proper now as i'm using my phone

 

[ddub25]

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

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

Thanks Scotty and Villareal, I've managed to get both of yours adapted and working (as above), but having a problem with my date formats. Both Y1 and the Range A5:A26 are in "mmm-yy"
format, but even though the dates in the range display as:

Sep-10
Oct-10
Nov-10
Dec-10
Jan-11
...
Aug-11 etc...

the actual date displayed in the formula bar is:

10/09/2010
10/10/2010
10/11/2010
10/12/2010
...
10/08/2011 etc...

If i put the date in Y1 as 23/11/11 (a date which does not exist in A5:A26 as we havent reached that month yet and therefore does not have a corresponding value in B5:B26) it returns the value
for Nov-10. It should return 0 for Nov-11 as it is empty.

Any ideas why this is? Do I need to simplify the date format to just Months/Years?

Dan