Assistance with Proper Formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David Lipetz - 10 Jul 2006 18:49 GMT

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

Reply to this Message

SteveG - 10 Jul 2006 19:19 GMT

David,

Try,

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

HTH

Steve

Signature

SteveG

Reply to this Message

David Lipetz - 10 Jul 2006 19:56 GMT

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!

> David,
>
[quoted text clipped - 5 lines]
>
> Steve

Reply to this Message

SteveG - 10 Jul 2006 20:42 GMT

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

Signature

SteveG

Reply to this Message

Rate this thread: