Relative reference in condition of SUMIF

Question

Level 1

10 points

Relative reference in condition of SUMIF

I need to SUM all the weekly totals. Each row has a date. In a hidden table, I calculate the WEEKNUM of the entry. Using SUMIF, I can add everything up. However, it appears I have to hard code the condition. For example:

=SUMIF(Table 5 :: A2:A37,"=30",Table 5 :: B2:B37)

Is there some way I can avoid "=30" and use some kind of reference?

A2:A37 is each entry's week number, B2:B37 is each entry's totals

dt

Mac mini, Mac OS X (10.5.7)

Posted on Jul 27, 2009 7:39 PM

Reply

Answer 1

Badunit

Level 7

31,002 points

Jul 27, 2009 10:51 PM in response to Doug Tallman

If the weeks are sequential, you can use ROW() plus an integer (ROW()+28 for instance if the formula was in row 2) to reference the week. You will also want to make the range absolute so instead of A2:A37 it would be A$2:A$37 or $A$2:$A$37 or you may be able to use a more simple A in place of the range which will reference the entire column. Same with the reference to B2:B37.

You would end up with

=SUMIF(Table 5 :: A, ROW()+28, Table 5 :: B)

then fill down to the rows below, no changes required.

Reply

Answer 2

KOENIG Yvan

Level 8

41,825 points

Jul 28, 2009 2:11 AM in response to Doug Tallman

The condition may be of this format:

"=<"&cellRef

So it will compare to the contents of the cell pointed by cellRef.

Yvan KOENIG (from FRANCE mardi 28 juillet 2009 11:10:54)

Reply

Answer 3

Doug Tallman Author

Level 1

10 points

Jul 28, 2009 3:27 AM in response to KOENIG Yvan

Worked. Thanks. Definitely going to save lots of time.

dt

Reply

Answer 4

KOENIG Yvan

Level 8

41,825 points

Jul 28, 2009 5:13 AM in response to Doug Tallman

Thanks for the feedback.

Reading pages 342/343 of the Formulas and Functions User Guide is a must !

Yvan KOENIG (from FRANCE mardi 28 juillet 2009 14:08:46)

Reply