I am attempting to do a conditional count (COUNTIF) on elements in a table produced by PowerQuery that I have subsequently sliced. COUNTIF doesn't register the slicer, which is problematic.

I found documentation on a workaround method using SUMPRODUCT and SUBTOTAL, but it doesn't seem to work the way that the documentation says it should. See here for the details on this method.

The function in the cell looks like this: =SUMPRODUCT(SUBTOTAL(2, OFFSET(C2:C13,ROW(C2:C13)-MIN(ROW(C2:C13)),,,1)),--(MOD(B2:B13, 3)=0))

My table is a list of whole numbers; B is 1-12 in order, C is all 1s.

This query produces a #VALUE error after the OFFSET step, but SUBTOTAL powers through it somehow and produces the wrong result for the formula (e.g., 23 when I'm expecting 3). The MOD function in the second part represents the condition that I might use in a COUNTIF statement.

I'm not sure why it's breaking, or for that matter why it's working at all when it evaluates to #VALUE halfway through. Can someone show me my mistake?

I am attempting to do a conditional count (COUNTIF) on elements in a table produced by PowerQuery that I have subsequently sliced. COUNTIF doesn't register the slicer, which is problematic.

I found documentation on a workaround method using SUMPRODUCT and SUBTOTAL, but it doesn't seem to work the way that the documentation says it should. See here for the details on this method.

The function in the cell looks like this: =SUMPRODUCT(SUBTOTAL(2, OFFSET(C2:C13,ROW(C2:C13)-MIN(ROW(C2:C13)),,,1)),--(MOD(B2:B13, 3)=0))

My table is a list of whole numbers; B is 1-12 in order, C is all 1s.

This query produces a #VALUE error after the OFFSET step, but SUBTOTAL powers through it somehow and produces the wrong result for the formula (e.g., 23 when I'm expecting 3). The MOD function in the second part represents the condition that I might use in a COUNTIF statement.

I'm not sure why it's breaking, or for that matter why it's working at all when it evaluates to #VALUE halfway through. Can someone show me my mistake?

• excel

• 1 OFFSET(C2:C13,ROW(C2:C13)-MIN(ROW(C2:C13)),,,1)) - removing the extraneous comma, make it two commas, not three, seems to fix the problem. – Michal Commented Apr 1 at 3:34
• 1 While, as you say, the OFFSET part of the formula returns the puzzling error, the SUBTOTAL evaluates to the correct result and removing the comma as per my previous comment fixes the problem - i.sstatic/ojqaJuA4.png – Michal Commented Apr 1 at 3:49

1 Answer 1

Credit to Michal for pointing out the extra comma in my function call. Removing it has made this function call work perfectly.

I find it frustrating that they haven't added a function that converts a normal reference to one with the sliced rows omitted. Having to use such a clunky workaround is nothing short of ridiculous.