Sumifs returning information from a list for linked condition

Question

Level 1

10 points

Sumifs returning information from a list for linked condition

Hi! I am trying to create a formula to take the sum of the information in Revenue, if the Job Type is the same as the Category listed in table 1. I'm working with a sumif function and would like to to sum all if (for example) job type is equal to "card", i want it to be a linked item so that i won't have to edit the field, but the first collumn of table 1.

My formula so far returns the correct answer, but requires that I put in the actual text: "card", heres the formula:
SUMIFS(Braithwaite Fine Art and Custom Frame::$I$2:$I$13,Braithwaite Fine Art and Custom Frame::$L$2:$L$13,"=card")

I'd like it to, where "=card" has been written, to take the text from table one and automatically take the word to the left and sum all totals from that information. Is this even possible? am I just expecting too much? haha
When put in the formula with the link (and it's around parentheses) it only returns to look for the word: "a2", which is not what i want. I want the word, from a2 or "card".

Thanks for your help!
Wes

Numbers 3.0 -OTHER, OS X Mavericks (10.9)

Posted on Nov 30, 2013 2:38 PM

Reply

Answer 1

Best reply

Jerrold Green1

Level 7

30,210 points

Nov 30, 2013 3:16 PM in response to Fjaxyu

FJ,

You are on the right track. You could have written this expression for the "Card" row in Table 1...

=SUMIFS(Braithwaite Fine Art and Custom Frame::$I$2:$I$13,Braithwaite Fine Art and Custom Frame::$L$2:$L$13, A)

If your Log table, Braithwaite Fine Art and Custom Frame, has 1 Header row and 1 Footer row, you can write:

=SUMIFS(Braithwaite Fine Art and Custom Frame::I,Braithwaite Fine Art and Custom Frame::L, A)

FILL DOWN.

Jerry

Reply

Answer 2

Fjaxyu Author

Level 1

10 points

Nov 30, 2013 5:06 PM in response to Fjaxyu

Thanks so much Jerrold! 😀 You're brilliant, I've been stuck for a good hour or so!

Reply

Answer 3

Barry

Level 9

59,861 points

Dec 1, 2013 6:00 PM in response to Fjaxyu

Hi Wes,

You could also place the text "Braithwaite Fine Art and Custom Frame" into a cell on "Table 1", then use INDIRECT to construct the two range addresses in your SUMIF formula. Doing that would let you change Table 1 (or a copy of Table 1) to references a different client/job table by changing only the content of the cell containing the client table name.

Regards,

Barry

Reply