Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple SUMIF help/advice
Hello
i need help with a function i have 3 columns A is a date like jan 2012, feb 2012, ... and so on B is an expense 10,00$, 20,00$, .... and so on C is a category like Fuel, House, DIY, Car Repair, ... and so on i need a function that sums all the expenses on a certain category in a year Thanks for your help!!!!!!!!! |
#2
|
|||
|
|||
Hi Lapo Zanuso
SUMPRODUCT will be the way to go, as SUMIF/SUMIFS cannot accept arrays. If you need to sum by year, the SUMIF and YEAR functions cannot be used together. Assuming your data is set up as your post: Col A = Date Col B = Expense Col C = Category Axample: =SUMPRODUCT(--(YEAR(A1:A100)=2012),--(C1:C100="DIY"),B1:B100) Quote:
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple SUMIF help/advice
Hi Kevin, thanks it works perfecly, and i thought i could transfer it to
Google Spreadsheet but there it dosent work anymore... do you have any suggestion? Lapo "Kevin@Radstock" wrote in message ... Hi Lapo Zanuso SUMPRODUCT will be the way to go, as SUMIF/SUMIFS cannot accept arrays. If you need to sum by year, the SUMIF and YEAR functions cannot be used together. Assuming your data is set up as your post: Col A = Date Col B = Expense Col C = Category Axample: =SUMPRODUCT(--(YEAR(A1:A100)=2012),--(C1:C100="DIY"),B1:B100) Lapo Zanuso;1608283 Wrote: Hello i need help with a function i have 3 columns A is a date like jan 2012, feb 2012, ... and so on B is an expense 10,00$, 20,00$, .... and so on C is a category like Fuel, House, DIY, Car Repair, ... and so on i need a function that sums all the expenses on a certain category in a year Thanks for your help!!!!!!!!! -- Kevin@Radstock |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple SUMIF help/advice
Solved for Google Spreadsheets using:
=SUM(FILTER(B:B;YEAR(A:A)=2012;C:C="DIY")) Thanks Lapo "Lapo Zanuso" wrote in message ... Hi Kevin, thanks it works perfecly, and i thought i could transfer it to Google Spreadsheet but there it dosent work anymore... do you have any suggestion? Lapo "Kevin@Radstock" wrote in message ... Hi Lapo Zanuso SUMPRODUCT will be the way to go, as SUMIF/SUMIFS cannot accept arrays. If you need to sum by year, the SUMIF and YEAR functions cannot be used together. Assuming your data is set up as your post: Col A = Date Col B = Expense Col C = Category Axample: =SUMPRODUCT(--(YEAR(A1:A100)=2012),--(C1:C100="DIY"),B1:B100) Lapo Zanuso;1608283 Wrote: Hello i need help with a function i have 3 columns A is a date like jan 2012, feb 2012, ... and so on B is an expense 10,00$, 20,00$, .... and so on C is a category like Fuel, House, DIY, Car Repair, ... and so on i need a function that sums all the expenses on a certain category in a year Thanks for your help!!!!!!!!! -- Kevin@Radstock |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple copying of a Spreadsheet advice please | Excel Discussion (Misc queries) | |||
Advice on sumif - more than one criteria | Excel Worksheet Functions | |||
Need multiple users advice | Excel Discussion (Misc queries) | |||
Need advice : consolidating data from multiple CSV files in Excel - External data handling | Excel Programming | |||
Advice sought: Multiple column sorting | Excel Worksheet Functions |