Sum If in a Budget
I'm trying to sum monthly amounts for a given month in my budget
spreadsheet, without success. Col A has DUE DATE (as dd/mm/yy) Col B has AMOUNT (as dollars) Col C has CATEGORIES (Rent, Utilities, Car etc) Elsewhere I have a table headed with MONTHS, and a list all my CATEGORIES. I want the table populated with $ amounts being the total of each CATEGORIES under each of the MONTHS cols. i.e. In cells G1-R1, I have col headers "Jan 09" through to "Dec 09", and In cell F2, I enter a category "Car" In cell G2, I want the total amount spent in "Jan 09" for "Car" Something like "Sum if DUE DATE is in JAN 09, and CATEGORY=CAR" Could someone help out with a formular that will achieve this. I realize I can get this result using filters, but this does not satisfy my reporting needs. Tks |
Sum If in a Budget
=Sumproduct(--(Text(DueDates, "mmm yy")=G$1), --(Categories=$F2), Amount)
replace Duedates, Categories, and Amount with the proper range references. Also, I'm assuming G1 is text. If it is actually text then replace G$1 with Text(G$1, "mmm yy") "Ricky" wrote: I'm trying to sum monthly amounts for a given month in my budget spreadsheet, without success. Col A has DUE DATE (as dd/mm/yy) Col B has AMOUNT (as dollars) Col C has CATEGORIES (Rent, Utilities, Car etc) Elsewhere I have a table headed with MONTHS, and a list all my CATEGORIES. I want the table populated with $ amounts being the total of each CATEGORIES under each of the MONTHS cols. i.e. In cells G1-R1, I have col headers "Jan 09" through to "Dec 09", and In cell F2, I enter a category "Car" In cell G2, I want the total amount spent in "Jan 09" for "Car" Something like "Sum if DUE DATE is in JAN 09, and CATEGORY=CAR" Could someone help out with a formular that will achieve this. I realize I can get this result using filters, but this does not satisfy my reporting needs. Tks |
Sum If in a Budget
Perfect JBM - thanks!
JMB wrote: =Sumproduct(--(Text(DueDates, "mmm yy")=G$1), --(Categories=$F2), Amount) replace Duedates, Categories, and Amount with the proper range references. Also, I'm assuming G1 is text. If it is actually text then replace G$1 with Text(G$1, "mmm yy") "Ricky" wrote: I'm trying to sum monthly amounts for a given month in my budget spreadsheet, without success. Col A has DUE DATE (as dd/mm/yy) Col B has AMOUNT (as dollars) Col C has CATEGORIES (Rent, Utilities, Car etc) Elsewhere I have a table headed with MONTHS, and a list all my CATEGORIES. I want the table populated with $ amounts being the total of each CATEGORIES under each of the MONTHS cols. i.e. In cells G1-R1, I have col headers "Jan 09" through to "Dec 09", and In cell F2, I enter a category "Car" In cell G2, I want the total amount spent in "Jan 09" for "Car" Something like "Sum if DUE DATE is in JAN 09, and CATEGORY=CAR" Could someone help out with a formular that will achieve this. I realize I can get this result using filters, but this does not satisfy my reporting needs. Tks |
Sum If in a Budget
Perfect JBM - thanks!
JMB wrote: =Sumproduct(--(Text(DueDates, "mmm yy")=G$1), --(Categories=$F2), Amount) replace Duedates, Categories, and Amount with the proper range references. Also, I'm assuming G1 is text. If it is actually text then replace G$1 with Text(G$1, "mmm yy") "Ricky" wrote: I'm trying to sum monthly amounts for a given month in my budget spreadsheet, without success. Col A has DUE DATE (as dd/mm/yy) Col B has AMOUNT (as dollars) Col C has CATEGORIES (Rent, Utilities, Car etc) Elsewhere I have a table headed with MONTHS, and a list all my CATEGORIES. I want the table populated with $ amounts being the total of each CATEGORIES under each of the MONTHS cols. i.e. In cells G1-R1, I have col headers "Jan 09" through to "Dec 09", and In cell F2, I enter a category "Car" In cell G2, I want the total amount spent in "Jan 09" for "Car" Something like "Sum if DUE DATE is in JAN 09, and CATEGORY=CAR" Could someone help out with a formular that will achieve this. I realize I can get this result using filters, but this does not satisfy my reporting needs. Tks |
Sum If in a Budget
you're welcome. also, i meant to say
If G1 is actually a *number* then replace G$1 with Text(G$1, "mmm yy") "Ricky" wrote: Perfect JBM - thanks! JMB wrote: =Sumproduct(--(Text(DueDates, "mmm yy")=G$1), --(Categories=$F2), Amount) replace Duedates, Categories, and Amount with the proper range references. Also, I'm assuming G1 is text. If it is actually text then replace G$1 with Text(G$1, "mmm yy") "Ricky" wrote: I'm trying to sum monthly amounts for a given month in my budget spreadsheet, without success. Col A has DUE DATE (as dd/mm/yy) Col B has AMOUNT (as dollars) Col C has CATEGORIES (Rent, Utilities, Car etc) Elsewhere I have a table headed with MONTHS, and a list all my CATEGORIES. I want the table populated with $ amounts being the total of each CATEGORIES under each of the MONTHS cols. i.e. In cells G1-R1, I have col headers "Jan 09" through to "Dec 09", and In cell F2, I enter a category "Car" In cell G2, I want the total amount spent in "Jan 09" for "Car" Something like "Sum if DUE DATE is in JAN 09, and CATEGORY=CAR" Could someone help out with a formular that will achieve this. I realize I can get this result using filters, but this does not satisfy my reporting needs. Tks |
Sum If in a Budget
Suggest you put all data into one worksheet and into columns eg. Col A has DUE DATE (as dd/mm/yy) Col B month Col C has AMOUNT (as dollars) Col D has CATEGORIES (Rent, Utilities, Car etc) for yr reporting, you are either use pivot table or sumproduct formula (very similar to sumif, except you can put more than multi-conditions). The above suggestion is also useful if you have to change your reporting dimension, you can also extra set of info by adding columns. I hope this help "Ricky" wrote in message . au... I'm trying to sum monthly amounts for a given month in my budget spreadsheet, without success. Col A has DUE DATE (as dd/mm/yy) Col B has AMOUNT (as dollars) Col C has CATEGORIES (Rent, Utilities, Car etc) Elsewhere I have a table headed with MONTHS, and a list all my CATEGORIES. I want the table populated with $ amounts being the total of each CATEGORIES under each of the MONTHS cols. i.e. In cells G1-R1, I have col headers "Jan 09" through to "Dec 09", and In cell F2, I enter a category "Car" In cell G2, I want the total amount spent in "Jan 09" for "Car" Something like "Sum if DUE DATE is in JAN 09, and CATEGORY=CAR" Could someone help out with a formular that will achieve this. I realize I can get this result using filters, but this does not satisfy my reporting needs. Tks |
All times are GMT +1. The time now is 11:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com