SUMIF with 2 Criteria

[razzandy]

Hi

Just cant get mi head round this, brain not working today!!

I want to sum lets say the the total in column C, if Column A matches a number and if Column B is above 0

Thanks in advance

Cheers

Ryan

I think I must need a SUMIF AND ???

 

[just_jon]

Hi

Just cant get mi head round this, brain not working today!!

I want to sum lets say the the total in column C, if Column A matches a number and if Column B is above 0

Thanks in advance

Cheers

Ryan

Try --

=SUMPRODUCT(--(A1:A1000=D1),--(B1:B1000>D2),C1:C1000)

Where D1 is your A column number criteria, D2 is your B column number [0] criteria and row 1000 is your end of data.

 

[razzandy]

Thanks just_jon

I cant get that to work and anyway the data list is constantly growing.

This is my formula up to now which works fine:

=SUMIF(Orders!$A:$A,Data1!$A$1,Orders!$R:$R)

I need to add a 'AND' statement somewhere something like below:

=SUMIF(Orders!$A:$A,Data1!$A$1,AND(Orders!$K:$K>0),Orders!$R:$R)


Thanks in advance

Ryan

 

[Barry Katcher]

Ryan, Jon's formula works just fine for me. If you are adding more data than thru row 1000, just expand the range in the formula.

Book1
	A	B	C	D	E	F	G
1	1	1	1			Col.A=	6
2	6	1	1			Col.B>	0
3	6	0	1			Result	4
4	4	4	1
5	2	2	1
6	6	1	1
7	6	1	2
Sheet1

 

[just_jon]

It is also possible to create dynamic [ ie, ever-growing/shrinking with your data ] range names, and use them in your formula.