| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF & IF
On column A I have dates (mmmm dd, yyyy), on column B I have names (John Doe)
and on column C I have ammounts ($0.00). On a seperate worksheet inside the same workbook, I would like to sum the ammounts per name, if they equal to =Today(). For example, A B C 1 July 20, 2009 John Doe $20 2 July 20, 2009 Jane Doe $10 3 July 28, 2009 John Doe $5 4 July 28, 2009 John Doe $20 5 July 28, 2009 Jane Doe $30 6 July 28, 2009 Jane Doe $10 July 28, 2009 ( =Today() ) John Doe - $25 Jane Doe - $40 Hope I got my idea through.... |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIF & IF
Assuming you have today's date in A1 of Sheet2, and then names below
starting in A2, put this formula in B2: =SUMPRODUCT((Sheet1!A$1:A$100=A$1)*(Sheet1!B$1:B$1 00=A2),Sheet1!C$1:C $100) Adjust to suit your data - I've assumed 100 rows. Copy down for as many individual names as you have in Sheet2. Hope this helps. Pete On Jul 28, 7:03*pm, GEM wrote: On column A I have dates (mmmm dd, yyyy), on column B I have names (John Doe) and on column C I have ammounts ($0.00). On a seperate worksheet inside the same workbook, I would like to sum the ammounts per name, if they equal to =Today(). For example, * * * A * * * * * * * * * * * *B * * * * * * * * *C 1 * *July 20, 2009 * * * John Doe * * * $20 2 * *July 20, 2009 * * * Jane Doe * * * $10 3 * *July 28, 2009 * * * John Doe * * * $5 4 * *July 28, 2009 * * * John Doe * * * $20 5 * *July 28, 2009 * * * Jane Doe * * * $30 6 * *July 28, 2009 * * * Jane Doe * * * $10 July 28, 2009 ( =Today() ) John Doe - $25 Jane Doe - $40 Hope I got my idea through.... |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIF & IF
Sheet1!A$1:A$99Hi,
In 2003: =SUMPRODUCT(--(Sheet1!A$1:A$99=Today()),--(Sheet1!B$1:B$99=A1),Sheet1!C$1:C$99) In 2007: =SUMIFS(Sheet1!C$1:C$99,Sheet1!A$1:A$99,Today(),Sh eet1!B$1:B$99,A1) where the names are in A1:A10 ... If you want you can enter =TODAY() in B1 and then replace it in the above formulas with B$1. =SUMIFS(Sheet1!C$1:C$99,Sheet1!A$1:A$99,B$1,Sheet1 !B$1:B$99,A1) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "GEM" wrote: On column A I have dates (mmmm dd, yyyy), on column B I have names (John Doe) and on column C I have ammounts ($0.00). On a seperate worksheet inside the same workbook, I would like to sum the ammounts per name, if they equal to =Today(). For example, A B C 1 July 20, 2009 John Doe $20 2 July 20, 2009 Jane Doe $10 3 July 28, 2009 John Doe $5 4 July 28, 2009 John Doe $20 5 July 28, 2009 Jane Doe $30 6 July 28, 2009 Jane Doe $10 July 28, 2009 ( =Today() ) John Doe - $25 Jane Doe - $40 Hope I got my idea through.... |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIF & IF
Is there a way to count this?? For example,
John Doe 2 Jane Doe 2 Because each one appeared 2 times today. "Pete_UK" wrote: Assuming you have today's date in A1 of Sheet2, and then names below starting in A2, put this formula in B2: =SUMPRODUCT((Sheet1!A$1:A$100=A$1)*(Sheet1!B$1:B$1 00=A2),Sheet1!C$1:C $100) Adjust to suit your data - I've assumed 100 rows. Copy down for as many individual names as you have in Sheet2. Hope this helps. Pete On Jul 28, 7:03 pm, GEM wrote: On column A I have dates (mmmm dd, yyyy), on column B I have names (John Doe) and on column C I have ammounts ($0.00). On a seperate worksheet inside the same workbook, I would like to sum the ammounts per name, if they equal to =Today(). For example, A B C 1 July 20, 2009 John Doe $20 2 July 20, 2009 Jane Doe $10 3 July 28, 2009 John Doe $5 4 July 28, 2009 John Doe $20 5 July 28, 2009 Jane Doe $30 6 July 28, 2009 Jane Doe $10 July 28, 2009 ( =Today() ) John Doe - $25 Jane Doe - $40 Hope I got my idea through.... |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIF & IF
Is there a way to count this?? For example,
John Doe 2 Jane Doe 2 Because each one appeared 2 times today. "Shane Devenshire" wrote: Sheet1!A$1:A$99Hi, In 2003: =SUMPRODUCT(--(Sheet1!A$1:A$99=Today()),--(Sheet1!B$1:B$99=A1),Sheet1!C$1:C$99) In 2007: =SUMIFS(Sheet1!C$1:C$99,Sheet1!A$1:A$99,Today(),Sh eet1!B$1:B$99,A1) where the names are in A1:A10 ... If you want you can enter =TODAY() in B1 and then replace it in the above formulas with B$1. =SUMIFS(Sheet1!C$1:C$99,Sheet1!A$1:A$99,B$1,Sheet1 !B$1:B$99,A1) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "GEM" wrote: On column A I have dates (mmmm dd, yyyy), on column B I have names (John Doe) and on column C I have ammounts ($0.00). On a seperate worksheet inside the same workbook, I would like to sum the ammounts per name, if they equal to =Today(). For example, A B C 1 July 20, 2009 John Doe $20 2 July 20, 2009 Jane Doe $10 3 July 28, 2009 John Doe $5 4 July 28, 2009 John Doe $20 5 July 28, 2009 Jane Doe $30 6 July 28, 2009 Jane Doe $10 July 28, 2009 ( =Today() ) John Doe - $25 Jane Doe - $40 Hope I got my idea through.... |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIF & IF
If you want a count of the names that appeared today, just miss off
the final term, i.e.: =SUMPRODUCT((Sheet1!A$1:A$100=A$1)*(Sheet1!B$1:B$1 00=A2)) Hope this helps. Pete On Jul 28, 8:13*pm, GEM wrote: Is there a way to count this?? For example, John Doe 2 Jane Doe 2 Because each one appeared 2 times today. "Pete_UK" wrote: Assuming you have today's date in A1 of Sheet2, and then names below starting in A2, put this formula in B2: =SUMPRODUCT((Sheet1!A$1:A$100=A$1)*(Sheet1!B$1:B$1 00=A2),Sheet1!C$1:C $100) Adjust to suit your data - I've assumed 100 rows. Copy down for as many individual names as you have in Sheet2. Hope this helps. Pete On Jul 28, 7:03 pm, GEM wrote: On column A I have dates (mmmm dd, yyyy), on column B I have names (John Doe) and on column C I have ammounts ($0.00). On a seperate worksheet inside the same workbook, I would like to sum the ammounts per name, if they equal to =Today(). For example, * * * A * * * * * * * * * * * *B * * * * * * * * *C 1 * *July 20, 2009 * * * John Doe * * * $20 2 * *July 20, 2009 * * * Jane Doe * * * $10 3 * *July 28, 2009 * * * John Doe * * * $5 4 * *July 28, 2009 * * * John Doe * * * $20 5 * *July 28, 2009 * * * Jane Doe * * * $30 6 * *July 28, 2009 * * * Jane Doe * * * $10 July 28, 2009 ( =Today() ) John Doe - $25 Jane Doe - $40 Hope I got my idea through....- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
| Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
| Help with SumIf | Excel Worksheet Functions | |||
| nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
| SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
Linear Mode
