Assistance with Proper Formula

[David Lipetz]

I'm stumped on which formula to use to perform this calculation.

On a sheet labeled Master, there are columns A-P with range of 3:356.
On another sheet labeled Dashboard, I am trying to create a formula which
will accomplish the following:

For all cells in sheet Master column K with a value =3, count non-blank
cells in sheet Master column A.

I've tried various COUNTIF and SUMPRODUCT formulas but am brainlocked on how
to do this rather simple calculation.

Your assistance would be greatly appreciated.

Thanks,
David

 

[SteveG]

David,

Try,

=SUMPRODUCT((Master!A3:A356<>"")*(Master!K3:K356=3))


HTH

Steve

 

[David Lipetz]

Thank you Steve. That did the trick. Can you shed some light on why this is
the right formula? I can't seem to grasp it.

Appreciated!

 

[SteveG]

David,
Thanks for the feedback.

The first test (A3:A356<>"") returns an array of TRUE/FALSE. The
second condition (K3:K356=3) will also return an array of TRUE/FALSE.
When multiplied together, TRUE*TRUE = 1 and FALSE * TRUE or FALSE
returns 0. That way when both conditions are true it returns 1.

A B
1 3
2 3

3 3
4 3

Returns (TRUE*TRUE)+(TRUE*TRUE)+(FALSE*FALSE)+(TRUE*TRUE)+(TRUE*TRUE)
or 4.

For more info see Bob Phillips site:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html


HTH,

Steve