Sumproduct - count unique

[The Horse]

Can't quite find the answer to my specific problem on here - help gratefully received...

Three columns of time-recording data:

A: Name
B: Project name
C: Project Live / Dead

Obviously, SUMPRODUCT((A2:A5000="MyName")*(C2:C5000="Live"))

gives me all the instances of "MyName" against live projects...how do I incorporate the project name element to get a count of the unique instances of:

"MyName""Live""Project ABC"
"MyName""Live""Project DEF"

etc ?

Thanks

Mick the Horse

 

[kidwispa]

Try

=SUMPRODUCT(--(A2:A5000="My Name"),--(B2:B5000="ABC"),--(C2:C5000="Live"))

This will give you all the instances of "My Name" where project is "ABC" and is live... just change the parameters to match what you are looking for...

 

[T. Valko]

Can't quite find the answer to my specific problem on here - help gratefully received...

Three columns of time-recording data:

A: Name
B: Project name
C: Project Live / Dead

Obviously, SUMPRODUCT((A2:A5000="MyName")*(C2:C5000="Live"))

gives me all the instances of "MyName" against live projects...how do I incorporate the project name element to get a count of the unique instances of:

"MyName""Live""Project ABC"
"MyName""Live""Project DEF"

etc ?

Thanks

Mick the Horse

It's not real clear what you're asking.

Can you post some sample data and tell us what result you expect?

 

[GlennUK]

If you want a count of how many unique project codes are in column B when column A is "MyName" and column C is "Live" then an array formula ( entered using Ctrl-Shift-Enter instead of Enter ) like this may do what you want:

=SUM(IF(FREQUENCY(IF(A2:A5000="MyName",IF(C2:C5000="Live",MATCH(B2:B5000,B2:B5000,0))),ROW(INDIRECT("1:"&ROWS(data!A2:A5000)))),1,0))

 

[The Horse]

Glenn,

absolutely on the money - thanks very much !!!

As a CSE formula, it does take a bit of calc time - don't suppose anyone knows a SUMPRODUCT version ?

However, Glenn, your formula works - so that's all that matters for now.

Kudos !!!

Mick the Horse

 

[T. Valko]

If you want a count of how many unique project codes are in column B when column A is "MyName" and column C is "Live" then an array formula ( entered using Ctrl-Shift-Enter instead of Enter ) like this may do what you want:

=SUM(IF(FREQUENCY(IF(A2:A5000="MyName",IF(C2:C5000="Live",MATCH(B2:B5000,B2:B5000,0))),ROW(INDIRECT("1:"&ROWS(data!A2:A5000)))),1,0))

Glenn,

absolutely on the money - thanks very much !!!

As a CSE formula, it does take a bit of calc time - don't suppose anyone knows a SUMPRODUCT version ?

However, Glenn, your formula works - so that's all that matters for now.

Kudos !!!

Mick the Horse

A SUMPRODUCT version would not be as efficient as the SUM/FREQUENCY version.

However, I would use this non-volatile version.

Still array enterd.

=SUM(IF(FREQUENCY(IF(A2:A5000="MyName",IF(C2:C5000="Live",MATCH(B2:B5000,B2:B5000,0))),ROW(B2:B5000)-ROW(B2)+1),1))

 

[The Horse]

Biff,


Presumably getting the INDIRECT function (the only volatile factor that I can spot) out of the formula speeds recalc up a little then ? This works also !

Your help was much appreciated !!

Mick the Horse

 

[T. Valko]

Biff,


Presumably getting the INDIRECT function (the only volatile factor that I can spot) out of the formula speeds recalc up a little then ? This works also !

Your help was much appreciated !!

Mick the Horse

Removing the volatile INDIRECT function will stop that already slow** formula from recalculating every time a calculation takes place.

** It's slow due to the size of the ranges involved.

Excel sorely needs an efficient built-in function to count uniques!