Conditional SUMIF???

[DaveO]

I have a sheet with a lot of data in it. It tracks the effectiveness of
certain campaigns we have. One of the key fields in here is Month.

What I'm trying to do is summarise the data for a board report. I have a
drop down of all of the months there are in the report. If I want to do a
particaular month's figures, I use a SUMIF and it gets the data fine. But if
I want to do a Year to date figure, how can I do this?

ie. I have all 2004 data. If I picked Aug-04 as a month to look at I'd want
to see Jan-04 to Aug-04 inclusive for my Year to Date figures and not also
include Sept-04 onwards.

Any ideas please guys??

TIA.

 

[Bob Phillips]

Do you have a date field, if so you can use

=SUMPRODUCT(--(TEXT(date_column,"yyyymm")<=200508),amt_column)

With SUMPRODUCT, you have to specify the cells, such as A2:A100, not a
complete column A:A.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[DaveO]

Thanks for the help Bob, but a little bit more to go yet....

I have results from 2003 to date. So how would I get this to work, bearing
in mind I need to do a >= to Jan of the year we're interested in and a <= to
the month in question.

I've looked at other SUMPRODUCTs but don't understand it. Currently I have
this...

=SUMPRODUCT(--('[Home Inbound.xls]Survey MAILINGS
2002-05'!$A$8:$P$10000>=VLOOKUP(B1,Sheet1!A:B,2,FALSE)), --('[Home
Inbound.xls]Survey MAILINGS 2002-05'!$A$8:$P$10000<=B1))

Let me explain.

Cell B2 is the drop down for the month we want to look at. The Vlookup looks
back to sheet1 to find the relevant Jan to start from as I have this by the
side of every month. So how would I factor this in?? Just not sure who to get
the valuie column in now!

Any help would be great.

 

[William Horton]

Creating a pivot table from the source data may be an easy way. You could
then select / deselect the months / years that you want to include / exclude.

Bill Horton

Thanks for the help Bob, but a little bit more to go yet....

I have results from 2003 to date. So how would I get this to work, bearing
in mind I need to do a >= to Jan of the year we're interested in and a <= to
the month in question.

I've looked at other SUMPRODUCTs but don't understand it. Currently I have
this...

=SUMPRODUCT(--('[Home Inbound.xls]Survey MAILINGS
2002-05'!$A$8:$P$10000>=VLOOKUP(B1,Sheet1!A:B,2,FALSE)), --('[Home
Inbound.xls]Survey MAILINGS 2002-05'!$A$8:$P$10000<=B1))

Let me explain.

Cell B2 is the drop down for the month we want to look at. The Vlookup looks
back to sheet1 to find the relevant Jan to start from as I have this by the
side of every month. So how would I factor this in?? Just not sure who to get
the valuie column in now!

Any help would be great.

Do you have a date field, if so you can use

=SUMPRODUCT(--(TEXT(date_column,"yyyymm")<=200508),amt_column)

With SUMPRODUCT, you have to specify the cells, such as A2:A100, not a
complete column A:A.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Assuming that you have the January date in C1, you could use

=SUMPRODUCT(--('[Home Inbound.xls]Survey MAILINGS
2002-05'!$A$8:$P$10000>=VLOOKUP(B1,Sheet1!A:B,2,FALSE)),
--('[Home Inbound.xls]Survey MAILINGS 2002-05'!$A$8:$P$10000>=C1)
--('[Home Inbound.xls]Survey MAILINGS 2002-05'!$A$8:$P$10000<=B1))


--

HTH

RP
(remove nothere from the email address if mailing direct)

Thanks for the help Bob, but a little bit more to go yet....

I have results from 2003 to date. So how would I get this to work, bearing
in mind I need to do a >= to Jan of the year we're interested in and a <= to
the month in question.

I've looked at other SUMPRODUCTs but don't understand it. Currently I have
this...

=SUMPRODUCT(--('[Home Inbound.xls]Survey MAILINGS
2002-05'!$A$8:$P$10000>=VLOOKUP(B1,Sheet1!A:B,2,FALSE)), --('[Home
Inbound.xls]Survey MAILINGS 2002-05'!$A$8:$P$10000<=B1))

Let me explain.

Cell B2 is the drop down for the month we want to look at. The Vlookup looks
back to sheet1 to find the relevant Jan to start from as I have this by the
side of every month. So how would I factor this in?? Just not sure who to get
the valuie column in now!

Any help would be great.

Do you have a date field, if so you can use

=SUMPRODUCT(--(TEXT(date_column,"yyyymm")<=200508),amt_column)

With SUMPRODUCT, you have to specify the cells, such as A2:A100, not a
complete column A:A.

--

HTH

RP
(remove nothere from the email address if mailing direct)


But
if

 

[DaveO]

Purley for completeness, here's how to do it guys.

Thanks for you help, it's been great!

=SUMPRODUCT(--('[Home Inbound1.xls]Survey MAILINGS
2002-05'!$A$8:$A$10000>=VLOOKUP(B1,Sheet1!A:B,2,FALSE)), --('[Home
Inbound1.xls]Survey MAILINGS 2002-05'!$A$8:$A$10000<=B1), '[Home
Inbound1.xls]Survey MAILINGS 2002-05'!$P$8:$P$10000)

Thanks for the help Bob, but a little bit more to go yet....

I have results from 2003 to date. So how would I get this to work, bearing
in mind I need to do a >= to Jan of the year we're interested in and a <= to
the month in question.

I've looked at other SUMPRODUCTs but don't understand it. Currently I have
this...

=SUMPRODUCT(--('[Home Inbound.xls]Survey MAILINGS
2002-05'!$A$8:$P$10000>=VLOOKUP(B1,Sheet1!A:B,2,FALSE)), --('[Home
Inbound.xls]Survey MAILINGS 2002-05'!$A$8:$P$10000<=B1))

Let me explain.

Cell B2 is the drop down for the month we want to look at. The Vlookup looks
back to sheet1 to find the relevant Jan to start from as I have this by the
side of every month. So how would I factor this in?? Just not sure who to get
the valuie column in now!

Any help would be great.

Do you have a date field, if so you can use

=SUMPRODUCT(--(TEXT(date_column,"yyyymm")<=200508),amt_column)

With SUMPRODUCT, you have to specify the cells, such as A2:A100, not a
complete column A:A.

--

HTH

RP
(remove nothere from the email address if mailing direct)