Sum If Multiple Ifs

[blarrabee]

Trying to sum numbers under the following conditions:


Sum If

2009!Q is greater than B and less than C

and 2009!I = A

any help would be appreciated.

Thanks!

 

[rdw72777]

If you search google, look for conditional sum wizard and download/instal it. its a wizard that walks you through stuff like this.

I'm sure someone on here will write up a nice array formula, but I use the wizard since its easier.

 

[VoG]

Try like this

=SUMPRODUCT(--(2009!Q1:Q100>B),--(2009!Q1:Q100 < C),--(2009!I1:I100=A),2009!X1:X100)<c),--(2009!i1:i100=a),2009!x1:x100)>

Replace A, B and C with numbers and change X1:X100 to the range to sum. All ranges must be the same size.</c),--(2009!i1:i100=a),2009!x1:x100)>

 

[T. Valko]

Trying to sum numbers under the following conditions:


Sum If

2009!Q is greater than B and less than C

and 2009!I = A

any help would be appreciated.

Thanks!

Do you want a SUM or a COUNT? If you want a SUM you left out what range should be summmed.

What version of Excel are you using?

 

[blarrabee]

Do you want a SUM or a COUNT? If you want a SUM you left out what range should be summmed.

What version of Excel are you using?

i want a count of A:A.

using excel 2008 for Mac.

 

[VoG]

=SUMPRODUCT(--(2009!Q1:Q100>B),--(2009!Q1:Q100 < C),--(2009!I1:I100=A),2009!A1:A100)

will give you a SUM.

 

[blarrabee]

What I think I want to do is:

=COUNTIFS('2009'!I:I,A2,'2009'!Q:Q,">=1/4/09",'2009'!AT:AT,"<=1/10/09")

but i'd like the dates to reference cells and not be "hard coded" into the formula.

 

[T. Valko]

Trying to sum numbers under the following conditions:


Sum If

2009!Q is greater than B and less than C

and 2009!I = A

any help would be appreciated.

Thanks!

i want a count of A:A.

using excel 2008 for Mac.

OK, still not clear!

Maybe this...

=SUMPRODUCT(--(2009!Q1:Q100>B1:B100),--(2009!Q1:Q100 < C1:C100),--(2009!I1:I100="A"))

 

[T. Valko]

What I think I want to do is:


but i'd like the dates to reference cells and not be "hard coded" into the formula.

OK, then just enter the dates in some cells:

• X1 = 1/4/09
• X2 = 1/10/09

Then:

=COUNTIFS('2009'!I:I,A2,'2009'!Q:Q,">="&X1,'2009'!AT:AT,"<="&X2)

 

[blarrabee]

OK, then just enter the dates in some cells:

• X1 = 1/4/09
• X2 = 1/10/09

Then:

=COUNTIFS('2009'!I:I,A2,'2009'!Q:Q,">="&X1,'2009'!AT:AT,"<="&X2)

perfect. thanks. forgot about the ampersands.