I need help with a SUMIF function that calculates the YTD sum referencing a
date (row) and a expense (col). The part I can't figure out is how to tell
it only to SUM the expenses for the dates previous to the date we tell it.

You need to use SUMPRODUCT. Explain how your data is laid out and someone
can be more specific.

Dave
--
Brevity is the soul of wit.


"Christy" wrote:

I need help with a SUMIF function that calculates the YTD sum referencing a
date (row) and a expense (col). The part I can't figure out is how to tell
it only to SUM the expenses for the dates previous to the date we tell it.

One way


=SUMIF(A2:A100,"<"&DATE(2006,12,31),B2:B100)

will sum B2:B100 for dates previous to December 31st 2006
You can replace the date formula with a cell where you would put the date

=SUMIF(A2:A100,"<"&C2,B2:B100)

where C2 holds the cut off date

if you want to include 12/31/06

use <= instead of <



Regards,

Peo Sjoblom

Christy wrote:
I need help with a SUMIF function that calculates the YTD sum referencing a
date (row) and a expense (col). The part I can't figure out is how to tell
it only to SUM the expenses for the dates previous to the date we tell it.

Thank you. The problem I have is the dates are the column headings and on
another sheet I created a cell named "date" where you type in the date to
pull information from the table. It also references the expense name from the
columns so This is the formula to get the current data for the date I type in
and for the expense which is in Col A on this sheet.

= INDEX('SGSS & Cashflow'!$B$7:$H$94,MATCH('Test GSR'!$A5,'SGSS &
Cashflow'!$B$7:$B$94,0),MATCH(date,'SGSS & Cashflow'!$B$6:$H$6,0))

Can I attach the spreadsheet here? This is my first time using this as you
can probably tell.

The index formula is in Column B on my "Test GSR" tab and I want to create a
YTD column C on this tab. The SGSS & Cashflow is the big table I am pulling
data from B7:H94. Column B has the expenses listed going down and row 6 has
the dates going across. I even transposed the table because the SUMIF
equation I found in a excel book only helped me with the column being summed.
Which is what your example shows. How do I tell it to sum the row that
references the "expense" in col A and everything to the right of the Date we
tell it.

I hope this makes sense- I really need help with this I have been working on
it for 5 days and have gotten nowhere!

"Peo Sjoblom" wrote:


One way


=SUMIF(A2:A100,"<"&DATE(2006,12,31),B2:B100)

will sum B2:B100 for dates previous to December 31st 2006
You can replace the date formula with a cell where you would put the date

=SUMIF(A2:A100,"<"&C2,B2:B100)

where C2 holds the cut off date

if you want to include 12/31/06

use <= instead of <



Regards,

Peo Sjoblom

Christy wrote:
I need help with a SUMIF function that calculates the YTD sum referencing a
date (row) and a expense (col). The part I can't figure out is how to tell
it only to SUM the expenses for the dates previous to the date we tell it.

Don't attach any files to a non binary newsgroups, you can upload a file
to a website and ask people to download it. It shouldn't be necessary
though, description should be enough

If you have the header dates in B6:H6 and you want to sum values in
B7:H94 previous to 12/31/06

=SUMPRODUCT((Sheet2!B6:H6<=--"12/31/06")*(Sheet2!B7:H94))


Regards,

Peo Sjoblom



Christy wrote:
Thank you. The problem I have is the dates are the column headings and on
another sheet I created a cell named "date" where you type in the date to
pull information from the table. It also references the expense name from the
columns so This is the formula to get the current data for the date I type in
and for the expense which is in Col A on this sheet.

= INDEX('SGSS & Cashflow'!$B$7:$H$94,MATCH('Test GSR'!$A5,'SGSS &
Cashflow'!$B$7:$B$94,0),MATCH(date,'SGSS & Cashflow'!$B$6:$H$6,0))

Can I attach the spreadsheet here? This is my first time using this as you
can probably tell.

The index formula is in Column B on my "Test GSR" tab and I want to create a
YTD column C on this tab. The SGSS & Cashflow is the big table I am pulling
data from B7:H94. Column B has the expenses listed going down and row 6 has
the dates going across. I even transposed the table because the SUMIF
equation I found in a excel book only helped me with the column being summed.
Which is what your example shows. How do I tell it to sum the row that
references the "expense" in col A and everything to the right of the Date we
tell it.

I hope this makes sense- I really need help with this I have been working on
it for 5 days and have gotten nowhere!

"Peo Sjoblom" wrote:

One way


=SUMIF(A2:A100,"<"&DATE(2006,12,31),B2:B100)

will sum B2:B100 for dates previous to December 31st 2006
You can replace the date formula with a cell where you would put the date

=SUMIF(A2:A100,"<"&C2,B2:B100)

where C2 holds the cut off date

if you want to include 12/31/06

use <= instead of <



Regards,

Peo Sjoblom

Christy wrote:
I need help with a SUMIF function that calculates the YTD sum referencing a
date (row) and a expense (col). The part I can't figure out is how to tell
it only to SUM the expenses for the dates previous to the date we tell it.

Ok thanks about attaching info.

It is giving me an error when I type in that formula. It says #VALUE!

The sheet I am in has column A with the expense types listed down it that I
also need to reference, it has a cell L1 which I have named date. This
formula doesn't reference the expense type so it doesn't know what row to
sum. Let me explain it again I think my other one didn't make sense.

I am on sheet 1 in cell C5. I want to tell it to reference the cell I
called date- which pulls from row C6:H6 on sheet 2. The I need it to
reference cell A5 to pull from B6:B94 on Sheet 2.

So when you wrote the first part it made sense to me to find where sheet2
C6:H6 <= date but then I need it to sum a specific row based on the reference
A5 seraching in B6:B94 for a match. Does this make any sense?

I really appreciate your help!

"Peo Sjoblom" wrote:

Don't attach any files to a non binary newsgroups, you can upload a file
to a website and ask people to download it. It shouldn't be necessary
though, description should be enough

If you have the header dates in B6:H6 and you want to sum values in
B7:H94 previous to 12/31/06

=SUMPRODUCT((Sheet2!B6:H6<=--"12/31/06")*(Sheet2!B7:H94))


Regards,

Peo Sjoblom



Christy wrote:
Thank you. The problem I have is the dates are the column headings and on
another sheet I created a cell named "date" where you type in the date to
pull information from the table. It also references the expense name from the
columns so This is the formula to get the current data for the date I type in
and for the expense which is in Col A on this sheet.

= INDEX('SGSS & Cashflow'!$B$7:$H$94,MATCH('Test GSR'!$A5,'SGSS &
Cashflow'!$B$7:$B$94,0),MATCH(date,'SGSS & Cashflow'!$B$6:$H$6,0))

Can I attach the spreadsheet here? This is my first time using this as you
can probably tell.

The index formula is in Column B on my "Test GSR" tab and I want to create a
YTD column C on this tab. The SGSS & Cashflow is the big table I am pulling
data from B7:H94. Column B has the expenses listed going down and row 6 has
the dates going across. I even transposed the table because the SUMIF
equation I found in a excel book only helped me with the column being summed.
Which is what your example shows. How do I tell it to sum the row that
references the "expense" in col A and everything to the right of the Date we
tell it.

I hope this makes sense- I really need help with this I have been working on
it for 5 days and have gotten nowhere!

"Peo Sjoblom" wrote:

One way


=SUMIF(A2:A100,"<"&DATE(2006,12,31),B2:B100)

will sum B2:B100 for dates previous to December 31st 2006
You can replace the date formula with a cell where you would put the date

=SUMIF(A2:A100,"<"&C2,B2:B100)

where C2 holds the cut off date

if you want to include 12/31/06

use <= instead of <



Regards,

Peo Sjoblom

Christy wrote:
I need help with a SUMIF function that calculates the YTD sum referencing a
date (row) and a expense (col). The part I can't figure out is how to tell
it only to SUM the expenses for the dates previous to the date we tell it.