Excel 2010 Cumulative SumIf

[PWD]

Good morning, I have found array formulae "out there" that enable me to count unique values from Column N in a filtered list

{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(N2,ROW(N2:N409)-ROW(N2),,1)),
IF(N2:N409<>"",MATCH("~"&N2:N409,N2:N409&"",0))),ROW(N2:N409)-ROW(N2)+1),1))}

which gives me 13.
and then just the unique values from Column N

{=SUM(IF(FREQUENCY(IF(LEN(N2:N409)>0,MATCH(N2:N409,N2:N409,0),""), IF(LEN(N2:N409)>0,MATCH(N2:N409,N2:N409,0),""))>0,1))}

which gives me 55.
What I'd like to be able to do is to add each (calendar) week's data (which can span over two calendar months) to a worksheet and then have a formula in another worksheet that looks at the Month (Column M) and produces a cumulative monthly figure.

I tried just wrapping a SUMPRODUCT around the first formula

{=SUMPRODUCT((SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(N2,ROW(N2:N409)-ROW(N2),,1)),
IF(N2:N409<>"",MATCH("~"&N2:N409,N2:N409&"",0))),ROW(N2:N409)-ROW(N2)+1),1)))*(M2:M409=5))}

but this gave me 5304

Schoolboy error or just overambitious?

In the attached workbook I'm just keeping the formulae on the same sheet for simplicity of testing.

Many thanks,
D€$

 

[PWD]

I've just tried

{=SUM(IF(FREQUENCY(IF(LEN(N2:N409)>0 * (M2:M409=5),MATCH(N2:N409,N2:N409,0),""), IF(LEN(N2:N409)>0 * (M2:M409=5),MATCH(N2:N409,N2:N409,0),""))>0,1))}

but the result is still 55 even when I change some of Column M from 5 to 4.

Many thanks,
D€$

 

[PWD]

OK, just found a simpler formula for getting a count of all the unique references

=SUMPRODUCT(1/COUNTIF(N2:N409,N2:N409))

Many thanks,
D€$

 

[PWD]

Hmm

=SUMPRODUCT((1/COUNTIF(N2:N409,N2:N409))*(M2:M409=5))

Results in 54 after I changed one record's (unfiltered) month from 5 to 4.

That's progress!!

Many thanks,
D€$