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
|