| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Identifying unique dates in a range of cells containing dates... | Excel Discussion (Misc queries) | |||
| Identifying unique dates within a range of cells containing dates | Excel Discussion (Misc queries) | |||
| need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
| Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
| Charting data against dates where dates are not at fixed intervals | Charts and Charting in Excel | |||
Linear Mode
