|
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.... |
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.... |
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.... |
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.... |
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.... |
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 - |
| All times are GMT +1. The time now is 03:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com