sum if x=val and y between 2 dates
 

I need to sum a range (column) of cells dependant on 3 criteria. I have spent
ages searching on here, but have become more and more confused!!

Sum all Column Q if cells in Column M=B1
and if cells in Column C =V2 and <V3

where V2 and V3 are start and end dates and B1 is a text string and columns
Q,M and C are on a different worksheet to B1, V2, V3 and the actual sumif
formula.

I have tried
=SUM(IF('Conferences
2007'!$M:$M=Totals!$B$1,IF($C:$C=Totals!V2,IF($C: $C<Totals!V3,$Q:$Q,))))

But just get '0'

Any ideas - thanks!

sum if x=val and y between 2 dates
 

=SUMPRODUCT(--(M1:M100=B1),--(C1:C100=V2),--(C1:C100<V3),Q1:Q100)
fix the ranges to suit your need but do not try to use full columns (as in
M:M ) as SUMPRODUCT cannot cope with that
For details on SUMPRODUCT see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"jenniebentham" wrote in message
...
I need to sum a range (column) of cells dependant on 3 criteria. I have
spent
ages searching on here, but have become more and more confused!!

Sum all Column Q if cells in Column M=B1
and if cells in Column C =V2 and <V3

where V2 and V3 are start and end dates and B1 is a text string and
columns
Q,M and C are on a different worksheet to B1, V2, V3 and the actual sumif
formula.

I have tried
=SUM(IF('Conferences
2007'!$M:$M=Totals!$B$1,IF($C:$C=Totals!V2,IF($C: $C<Totals!V3,$Q:$Q,))))

But just get '0'

Any ideas - thanks!

sum if x=val and y between 2 dates
 

Jennie try

=SUMPRODUCT(--('Conferences 2007'!M1:M65536=Totals!V2),('Conferences
2007'!M1:M65536<=Totals!V2))*(Totals!Q1:Q65536))

I am not sure that I have the sheets right

Peter


"jenniebentham" wrote:

I need to sum a range (column) of cells dependant on 3 criteria. I have spent
ages searching on here, but have become more and more confused!!

Sum all Column Q if cells in Column M=B1
and if cells in Column C =V2 and <V3

where V2 and V3 are start and end dates and B1 is a text string and columns
Q,M and C are on a different worksheet to B1, V2, V3 and the actual sumif
formula.

I have tried
=SUM(IF('Conferences
2007'!$M:$M=Totals!$B$1,IF($C:$C=Totals!V2,IF($C: $C<Totals!V3,$Q:$Q,))))

But just get '0'

Any ideas - thanks!

sum if x=val and y between 2 dates
 

Thanks so much guys, I'm not back at work til Tuesday, so I shall have a go
then.

"Bernard Liengme" wrote:

=SUMPRODUCT(--(M1:M100=B1),--(C1:C100=V2),--(C1:C100<V3),Q1:Q100)
fix the ranges to suit your need but do not try to use full columns (as in
M:M ) as SUMPRODUCT cannot cope with that
For details on SUMPRODUCT see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"jenniebentham" wrote in message
...
I need to sum a range (column) of cells dependant on 3 criteria. I have
spent
ages searching on here, but have become more and more confused!!

Sum all Column Q if cells in Column M=B1
and if cells in Column C =V2 and <V3

where V2 and V3 are start and end dates and B1 is a text string and
columns
Q,M and C are on a different worksheet to B1, V2, V3 and the actual sumif
formula.

I have tried
=SUM(IF('Conferences
2007'!$M:$M=Totals!$B$1,IF($C:$C=Totals!V2,IF($C: $C<Totals!V3,$Q:$Q,))))

But just get '0'

Any ideas - thanks!

sum if x=val and y between 2 dates
 

Thank you, thank you, thank you!


"Bernard Liengme" wrote:

=SUMPRODUCT(--(M1:M100=B1),--(C1:C100=V2),--(C1:C100<V3),Q1:Q100)
fix the ranges to suit your need but do not try to use full columns (as in
M:M ) as SUMPRODUCT cannot cope with that
For details on SUMPRODUCT see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"jenniebentham" wrote in message
...
I need to sum a range (column) of cells dependant on 3 criteria. I have
spent
ages searching on here, but have become more and more confused!!

Sum all Column Q if cells in Column M=B1
and if cells in Column C =V2 and <V3

where V2 and V3 are start and end dates and B1 is a text string and
columns
Q,M and C are on a different worksheet to B1, V2, V3 and the actual sumif
formula.

I have tried
=SUM(IF('Conferences
2007'!$M:$M=Totals!$B$1,IF($C:$C=Totals!V2,IF($C: $C<Totals!V3,$Q:$Q,))))

But just get '0'

Any ideas - thanks!