multiple SUMIF help/advice

L

Lapo Zanuso

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!!!!!!!!!
 
K

Kevin@Radstock

Hi Lapo Zanus

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 use
together. Assuming your data is set up as your post

Col A = Dat
Col B = Expens
Col C = Categor

Axample

=SUMPRODUCT(--(YEAR(A1:A100)=2012),--(C1:C100="DIY"),B1:B100)
 
L

Lapo Zanuso

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)
 
L

Lapo Zanuso

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top