sum if x=val and y between 2 dates

[Guest]

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!

 

[Bernard Liengme]

=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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Thank you, thank you, thank you!

=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