I am trying to take the data from one sheet and give a value in a second
sheet.

For example, I have the following data.
PAID WHEN HOW
$13.00 Jan Check
$52.00 Jan Check
$11.88 Jan Cash
$13.00 Jan Cash
$13.00 Jan Cash

I want to get the following values on the second sheet:
2007 Collected Cash Check
Jan $102.88 $37.88 $65.00
Feb $0.00
Mar $0.00


This is the formula I currently have but need to elaborate on it:
=SUMIF('2007'!D6:D85,"=Jan",'2007'!C6:C85)

This will only give me the information for the amount collected (total) that
has a WHEN = JAN. I would like to know what the value is if the WHEN = JAN
and the HOW = CASH.

I don't know if I need to use a different function.

Thanks, Karen

Hi Karen

Depending upon how you have the When entered.
If it is in the form of an Excel Date, e.g. 01 Jan 2007 or 01/01/2007 or
your regional settings equivalent, then on sheet2 in cell A2 enter
dates in a similar format for each of the 12 months.

Then in cell C2 of Sheet2
=SUMPRODUCT((TEXT(Sheet1!$B$2:$B$100,"dd mmm yy"=TEXT(A2,"dd mmm yy")*
(Sheet1!$C$2:$C$100=C$1)*($A$2:$A$100))
Copy across to cell D2

In cell B2
=C2+D2
Copy B2:D2 down through B3:B13

If the When is just Text "Jan" then enter text "Jan" on sheet2 and use
=SUMPRODUCT((Sheet1!$B$2:$B$100=A2)*
(Sheet1!$C$2:$C$100=C$1)*($A$2:$A$100))

But in this scenario I don't see where the 2007 comes into the equation
at all.

--
Regards

Roger Govier


"Karen" wrote in message
...
I am trying to take the data from one sheet and give a value in a
second
sheet.

For example, I have the following data.
PAID WHEN HOW
$13.00 Jan Check
$52.00 Jan Check
$11.88 Jan Cash
$13.00 Jan Cash
$13.00 Jan Cash

I want to get the following values on the second sheet:
2007 Collected Cash Check
Jan $102.88 $37.88 $65.00
Feb $0.00
Mar $0.00


This is the formula I currently have but need to elaborate on it:
=SUMIF('2007'!D6:D85,"=Jan",'2007'!C6:C85)

This will only give me the information for the amount collected
(total) that
has a WHEN = JAN. I would like to know what the value is if the WHEN =
JAN
and the HOW = CASH.

I don't know if I need to use a different function.

Thanks, Karen

Karen,

I would concatenate the when and how columns, so in cell d2 you would have =concatenate(b2,c2) then set that column as your range 1. So then if you =sumif(range1,"jancheck",range2) you would just get your jan checks. You can concatenate in the sumif as well so that you can just copy the formula down.

Thanks!


"Roger Govier" wrote:

Hi Karen

Depending upon how you have the When entered.
If it is in the form of an Excel Date, e.g. 01 Jan 2007 or 01/01/2007 or
your regional settings equivalent, then on sheet2 in cell A2 enter
dates in a similar format for each of the 12 months.

Then in cell C2 of Sheet2
=SUMPRODUCT((TEXT(Sheet1!$B$2:$B$100,"dd mmm yy"=TEXT(A2,"dd mmm yy")*
(Sheet1!$C$2:$C$100=C$1)*($A$2:$A$100))
Copy across to cell D2

In cell B2
=C2+D2
Copy B2:D2 down through B3:B13

If the When is just Text "Jan" then enter text "Jan" on sheet2 and use
=SUMPRODUCT((Sheet1!$B$2:$B$100=A2)*
(Sheet1!$C$2:$C$100=C$1)*($A$2:$A$100))

But in this scenario I don't see where the 2007 comes into the equation
at all.

--
Regards

Roger Govier


"Karen" wrote in message
...
I am trying to take the data from one sheet and give a value in a
second
sheet.

For example, I have the following data.
PAID WHEN HOW
$13.00 Jan Check
$52.00 Jan Check
$11.88 Jan Cash
$13.00 Jan Cash
$13.00 Jan Cash

I want to get the following values on the second sheet:
2007 Collected Cash Check
Jan $102.88 $37.88 $65.00
Feb $0.00
Mar $0.00


This is the formula I currently have but need to elaborate on it:
=SUMIF('2007'!D6:D85,"=Jan",'2007'!C6:C85)

This will only give me the information for the amount collected
(total) that
has a WHEN = JAN. I would like to know what the value is if the WHEN =
JAN
and the HOW = CASH.

I don't know if I need to use a different function.

Thanks, Karen