0

This is the formula that I am currently using:

=SUMPRODUCT((INDIRECT("A2"):INDIRECT("A"&(ROW()-1))=A359)*1)

It works great, but I would like to use this instead:

=SUMPRODUCT((INDIRECT("A2"):INDIRECT("A"&(ROW()-1))=INDIRECT("A"&(ROW())))*1)

Unfortunately I get a #VALUE!. What am I doing wrong? Both INDIRECT("A"&ROW(())) and A359 return the same value, so I'm not sure why this won't work.

The reason I am not using a simple COUNTIF function is because I stripped my formula of all unnecessary components and only left the part that I am having trouble with (i.e. I need to use the SUMPRODUCT formula and a COUNTIF formula will not work)

Thanks in advance!

Nate
  • 3
  • 1
  • 3
  • Nothing seems wrong to me, and it seems to be working fine on my machine. (Btw you could use A2 instead of `INDIRECT("A2")`) and `ROW()` instead of `(ROW())`. – Jerry Mar 30 '14 at 15:54
  • I am using `INDIRECT("A2"):INDIRECT("A"&(ROW()-1))` instead of `$A$2:$A358` because I copy, paste, and delete this formula from different sheets all the time. If I were to leave in the normal cell reference `A2`, then it would get messed up every time I moved/deleted the formula. – Nate Mar 30 '14 at 15:59
  • but INDIRECT makes your formula volatile and it recalculates each time _any_ cell in the sheet changed (and recalculation of all formulas may be time consuming). – Dmitry Pavliv Mar 30 '14 at 16:00
  • Weird, it's not working for me. Please see this file [link](https://app.box.com/s/uqcd0a7o846xkk1e82nc) – Nate Mar 30 '14 at 16:08

2 Answers2

4

I'm not sure why you need INDIRECT instead of ordinary cell references but the specific problem here is that ROW function returns an "array", even when it returns a single value, e.g. it returns {"x"} rather than just "x" - and in some circumstances Excel can't process that.

Try wrapping the second ROW function in a SUM function - the value doesn't change but it gets rid of the array, i.e.

=SUMPRODUCT((INDIRECT("A2"):INDIRECT("A"&(ROW()-1))=INDIRECT("A"&SUM(ROW())))*1)

This will eliminate #VALUE! eror while leaving the essential structure of your formula unchanged

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • I never realised that `ROW()` returned an array. Learned something new today! :) – Jerry Mar 30 '14 at 16:04
  • 1
    @Jerry - in most cases it makes no difference so you wouldn't notice - `COLUMN` function does the same (whereas `ROWS` and `COLUMNS` do not) – barry houdini Mar 30 '14 at 16:35
1

Try this one:

=SUMPRODUCT((($A$2:INDEX($A:$A,ROW()-1))=INDEX($A:$A,ROW()))*1)

it gives you the same result.

But above formula is volatile. Instead I would use next one, say in B3 :

=SUMPRODUCT((($A$2:$A2)=$A3)*1)
Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80