Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
|
|||
|
|||
Quote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use the sumif if I have multiple criteria (i.e. greater . | Excel Worksheet Functions | |||
Multiple SUMIF Criteria | Excel Worksheet Functions | |||
SUMIF with Mutiple Ranges & Criteria | Excel Discussion (Misc queries) | |||
"criteria" in a sumif refering to the value in another cell | Excel Discussion (Misc queries) | |||
Can I use a cell reference in the criteria for the sumif function. | Excel Worksheet Functions |