David Graeme-Baker
Level 1 (119 points)
Mac OS X

Q: That SUMIF function - an idiot question

This is my first attempt at spreadsheeting and like my first attempts at skateboarding I seem doomed.
However I have persevered and with help from forum members have made a very useful financial tool.

I now have a spreadsheet detailing expenditure on a building project - pretty simple stuff.

And thanks to some helpful people on this forum I have managed to create extra tables that add supplier costs, so I can see how much I spent at "Jewsons"builders merchant and also at "Acorn"who supply waste removal.

And the formula looks like this:

Screen Shot 2016-09-06 at 15.50.13.png

That table returns the total of money I have spent on skips with the waste removal company called Acorn.

I'm really pleased at how that works

So pleased in fact I wanted to create another table that gets info from a different column and adds it up.
I am trying to create SUMIF ITEM(thats the column name), "Lighting",Cost.

I want to total my spend on Lighting (which appears in the Item column)

So the spreadsheet should look in column "ITEM" for any reference to "Lighting"and total the costs in a separate table.

 

 

Screen Shot 2016-09-06 at 15.44.55.png

 

In an effort to make life simple for myself I took one of my existing working tables that creates totals from the "Supplier" column and then duplicated it, gave it a new name and then attempted to edit it to give me the result I wanted.Thought that would be easy: Replace "Supplier" with "Item"and the search term "Acorn" with the new term "lighting".

Doesn't seem to work like that though.

I open the formula and highlight "Supplier" and then click on "Item" in my main table in the hope that "Item" would then replace "Supplier", instead I get "Table1::A1" and it doesn't work !!

 

Tried various other combinations of clicking and cussing but nothing seems to allow me to edit my standalone tables to another role.

 

Now I do know the answer will be simple and straightforward but as far as spreadsheets go I am a complete idiot and this is the very first one I have ever used.

So be gentle with me please, and tell me how to do it.

Thanks

Mac Pro, OS X El Capitan (10.11.1), 1 Tb SSD

Posted on Sep 6, 2016 8:16 AM

Close
David Graeme-Baker

Q: That SUMIF function - an idiot question

  • All replies
  • Helpful answers

  • by t quinn,Helpful

    t quinn t quinn Sep 6, 2016 9:32 AM in response to David Graeme-Baker
    Level 5 (4,930 points)
    Mac OS X
    Sep 6, 2016 9:32 AM in response to David Graeme-Baker

    Hi David,

     

    One of the things I love about Numbers is how easy it can be to create formulas. Point and click is where it is at for me.

    First I wnat to suggest you look at the personal budget template. It uses SUMIF() quite a bit to gather totals.

    Screen Shot 2016-09-06 at 9.27.04 AM.png

    Notice the difference in my formula. In Table 2::B2 my SUMIF looks in Table 1::A for the value in Table 2::A2. When it finds it, it sums the values in Table 1::B. If Clients was Items and "A" was "lighting" we would be done.

    A note. If you are searching for "lighting" in a cell that contains "Acme Lighting" you will not return a value.

     

    This is how I build a formula.

    Screen Shot 2016-09-06 at 9.34.20 AM.png

    I have typed "=" in B2 and then "sumif" and hit return.

    "test values' is highlighted so I go to Table 1 and click column A. These are that values I will search.

    Next I click "condition" to highlight it and click A2 in this table because that is the value I will search for.

    Last I click "sum values" and choose column B in Table 1. I will be seeing  what is shown in my first screenshot.

    Very last I hit return.

     

    Does this help with your question?

     

    quinn

  • by David Graeme-Baker,

    David Graeme-Baker David Graeme-Baker Sep 6, 2016 9:32 AM in response to t quinn
    Level 1 (119 points)
    Mac OS X
    Sep 6, 2016 9:32 AM in response to t quinn

    Thanks Quinn

    I'm not yet sure if this helps me but I will attempt to work through it line by line and see if I can get it to work for me.

    Still unsure as to why I can't get "Item" to appear in my table as the look up column whereas somehow,previously, I managed to get "Supplier" in the formula.

    Oh well I will persevere and see what happens.