Why won't ADDRESS work nested inside SUMIF?

  • Thread starter Thread starter Beric Dondarrion
  • Start date Start date
B

Beric Dondarrion

If I do this:

=ADDRESS(4,MATCH(E37,A2:JW2,0))

I get:

$L$4

If I do:

=SUMIF(A3:JW3,"Units",ADDRESS(4,MATCH(E37,A2:JW2,0)):JW4)

I get an error. However if I do:

=SUMIF(A3:JW3,"Units",INDIRECT(G1):JW4)

I get the answer I want. However, I'd prefer to not have a helper cell.

Please help!
 
Some lateral thoughts, with emphasis on resolving the core issues you raised
in your other, earlier thread

Let's say you have col data in groups of 3 cols, with headers in row2 (eg:
Period1, Period2, etc), where each header is placed only in the leftmost cell
for the group, eg:

Period1 Period2
9 88 76 6 99 11
1 22 44 5 88 23
etc

To sum the col data corresponding to a specific header (eg: Period1),
you could use* these triplets:
=SUM(OFFSET(A:A,,MATCH("Period1",2:2,0)-1))
=SUM(OFFSET(A:A,,MATCH("Period1",2:2,0)))
=SUM(OFFSET(A:A,,MATCH("Period1",2:2,0)+1))
*placed to avoid circular ref errors, of course

The 1st will return the leftmost col's sum for Period1's group, the 2nd
returns the middle col's sum, and the 3rd returns the rightmost col's sum.
All 3 expressions are similar, where we adjust the OFFSET's col param
returned by the MATCH (that's the: +1,-1 bits) to suit the particular col to
"grab" for the sum. Instead of hardcoding "Period1" in the expressions, you
could of course point to another cell/DV returning the col header for
flexibility. Useful? thump the YES below
 
I appreciate your help. And I apologize for not presenting the problem
accurately.

I want to sum the row data, not the column data. So in your example, I want
to sum the 9 from the Period 1 and the 6 from period 2, the 88 and 99, the 76
and 11. And continue that until there's no more data (Period 1 is the oldest
and farthest to the right). Each column has it's own label. So it'd look
like:

Period 5 Period 4 ...
Units Cost Hours Units Cost Hours
9 88 76 6 99 11


However, I figured out how to do that (see my first post). The problem is
that I still need a helper cell to do it.

Thanks again,

Beric
 
Back
Top