Sumproduct with a twist

[rebelroy]

Hi all,

I have a sumproduct formula in a cell that returns a count of the number of times several columns satisfy certain conditions.

One column referred to in the formula contains a unique identifier.

How can I tweak the sumproduct formula so that it will ignore any repeat instances in the unique identifier column?

Thanks

 

[lenze]

Can you please post your formula?
lenze

 

[rebelroy]

Hi Lenze,

Thanks for your reply.

My formula is:

=SUMPRODUCT((A3:A300="On")*(B3:B300="M")*(D3:D300="18-25")*(F3:F300="Inside"))

Now I'm afraid I've mislead you in my first post. The unique idendifier is in column C and so is not part of the formula.

Now if the unique identifier is duplicated anywhere in C3:3000, I want the formula to ignore the duplicate(s). Therefore, I presume that I must include column C in the formula but how?

Thanks for your help.

 

[barry houdini]

Try this formula

=SUM(IF(FREQUENCY(IF((A3:A300="On")*(B3:B300="M")*(D3:D300="18-25")*(F3:F300="Inside")*(C3:C300<>""),MATCH(C3:C300,C3:C300,0)),ROW(C3:C300)-ROW(C3)+1),1))

This is an "array formula" that needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar.....

 

[rebelroy]

Thanks Barry.

I'll have to wait till tomorrow to try that one. I'll get back to you then.

Thanks again.

 

[rebelroy]

Hi Barry,

It works fine, thanks. I'm not sure I follow what you've done entirely. Would you mind explaining your formula please?