SUMIF need to add another criteria
 

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

SUMIF need to add another criteria
 

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

SUMIF need to add another criteria
 

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

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.