Numbers referencing formula question

[Madwolfie]

Hi
I am struggling to get this to work how I want it to - anyone any ideas?

I have 2 columns, A has prices of sold items in it, B has the date item sold (just the year). I am trying to provide a result where I can get a SUM of the year using column A, but referencing which year from column B.

Result: ideally, woud be separate totals for each year, of all that has been sold, of all the prices in column A

Thanks in advance

 

[James_C]

Hi, you can do this using the SUMIF formula.

SUMIF( Range of Years, Year Criteria, Data Range)

See screenshot below;

 

[Madwolfie]

Hi, you can do this using the SUMIF formula.

SUMIF( Range of Years, Year Criteria, Data Range)

Hi James
Thank you so much - I will have a go - that looks very neat and it saves me adding so many extra coloumns for each year and then moving all the items sold into each appropriate year - which was the only solution I could come up with.
Even neater that it works with currency too - now for the extra fun bit - I am an artist and the items sold can be by me - but also by Galleries (with the 40% deducted automatically), so there is another column 'O' which has the paintings sold by Galleries in then 'P' which are the ones sold by me and the year column is 'Q'.
Would it be possible to add another range to reference those in the Gallery column 'O' adding those to the same total in P87 ??

SUMIF(Q$2:Q$83,P87,P$2$83)
Cheers
Colin

 

[James_C]

Hi Colin,

OK I have tried to interpret what you have said above. I am assuming that you have the prices you sold in Col P and the Net Value (Sales price less 20% commission) given to you by the Galleries in Col O

In the example below the 'Y' in Column A indicates your own sales and I have formula in C and D columns to calculate the net sales value.

My Col C hopefully is what you have in your Col P (Value of own sales)
My Col D hopefully is what you have in your Col O (Net Value of Gallery sales)

First screenshot just replicates what we had before, gross sales before any commission deductions in the summary at the bottom.

Next one Calculates the value of your own sales by year in Cell D22 for 2016

Then Next Column Calculates the value of the gallery net sales in E22

I then added some formula to calculate the Galleries commission and the commission %, to make sure it is 20%

I hope that helps - If I have misunderstood your spreadsheet let me know and I will update the calculations.

It is probably worth mentioning that if you want to do this type of analysis it may be worth looking at Excel that has some specific functionality to analyse data in tables called Pivot Tables that is sadly missing in Numbers at this time.

 

[Madwolfie]

Thank you James so much - almost a full accounting solution !! Except the Galleries take 40% not 20 !!
I will have a thorough read after I have finished this piece I am working on.
My accountant son-in-law says the same about Numbers vs Excel
Take care
Colin

 

[James_C]

Thank you James so much - almost a full accounting solution !! Except the Galleries take 40% not 20 !!

No problem - happy to help, sorry I got the % wrong, however the principle remains the same I am a Chartered Accountant so I live in spreadsheets

 

[Madwolfie]

Yes, it had that feel to it ;-) Though it is rare for an accountant to understand how I work !
I love the challenge of getting something to work, but time is my enemy and I am better at painting than numbers for sure ! Thanks again