Sumproduct
 

I use this formula:

=SUMPRODUCT(--(Sheet1!$Y$4:$Y$903=Sheet2!G$10);--(Sheet1!$AB$4:$AB$903="Directed");--(Sheet1!$I$4:$I$903=Sheet2!E11);--(Sheet1!$AC$4:$AC$903=60);Sheet1!$N$4:$N$903)

The values in Col N are not numbers. The formula returns 0 (zero).

Is there a way to modify the formula to count the number of cells that
satisfy the 4 criteria ?

Thank you in advance.

Sumproduct
 

First, I don't know why it's even letting you commit that formula with
semi-colon's to separate each condition. SUMPRODUCT is set up to allow
comma's for the breaks, so you might want to change that.

Second, the way you have the formula set up now, it sums Sheet1!$N$4:$N$903
if all 4 conditions are met (which is why it's returning 0, because there are
no numerical values). What are you actually trying to do with the values in
column N? You need to specifially set a condition for what you are trying to
count. For example, if you are trying to count the number of non-missing
values in column N when all 4 conditions are met, change to this:

--(Sheet1!$N$4:$N$903<"")

Does that make sense?
--
Regards,
Dave


"carl" wrote:

I use this formula:

=SUMPRODUCT(--(Sheet1!$Y$4:$Y$903=Sheet2!G$10);--(Sheet1!$AB$4:$AB$903="Directed");--(Sheet1!$I$4:$I$903=Sheet2!E11);--(Sheet1!$AC$4:$AC$903=60);Sheet1!$N$4:$N$903)

The values in Col N are not numbers. The formula returns 0 (zero).

Is there a way to modify the formula to count the number of cells that
satisfy the 4 criteria ?

Thank you in advance.

Sumproduct
 

Assuming that your version of Excel uses a semi-colon instead of a comma
as a separator, and that the '4 criteria' refers to Columns Y, AB, I,
and AC, try..

=SUMPRODUCT(--(Sheet1!$Y$4:$Y$903=Sheet2!G$10);--(Sheet1!$AB$4:$AB$903="D
irected");--(Sheet1!$I$4:$I$903=Sheet2!E11);--(Sheet1!$AC$4:$AC$903=60))

Hope this helps!

In article ,
"carl" wrote:

I use this formula:

=SUMPRODUCT(--(Sheet1!$Y$4:$Y$903=Sheet2!G$10);--(Sheet1!$AB$4:$AB$903="Direct
ed");--(Sheet1!$I$4:$I$903=Sheet2!E11);--(Sheet1!$AC$4:$AC$903=60);Sheet1!$N$4
:$N$903)

The values in Col N are not numbers. The formula returns 0 (zero).

Is there a way to modify the formula to count the number of cells that
satisfy the 4 criteria ?

Thank you in advance.

Sumproduct
 

Thank you. It does make sense.

Any chance I can set the criteria --(Sheet1!$N$4:$N$903<"") to only count
Unique Values ?

"David Billigmeier" wrote:

First, I don't know why it's even letting you commit that formula with
semi-colon's to separate each condition. SUMPRODUCT is set up to allow
comma's for the breaks, so you might want to change that.

Second, the way you have the formula set up now, it sums Sheet1!$N$4:$N$903
if all 4 conditions are met (which is why it's returning 0, because there are
no numerical values). What are you actually trying to do with the values in
column N? You need to specifially set a condition for what you are trying to
count. For example, if you are trying to count the number of non-missing
values in column N when all 4 conditions are met, change to this:

--(Sheet1!$N$4:$N$903<"")

Does that make sense?
--
Regards,
Dave


"carl" wrote:

I use this formula:

=SUMPRODUCT(--(Sheet1!$Y$4:$Y$903=Sheet2!G$10);--(Sheet1!$AB$4:$AB$903="Directed");--(Sheet1!$I$4:$I$903=Sheet2!E11);--(Sheet1!$AC$4:$AC$903=60);Sheet1!$N$4:$N$903)

The values in Col N are not numbers. The formula returns 0 (zero).

Is there a way to modify the formula to count the number of cells that
satisfy the 4 criteria ?

Thank you in advance.

Sumproduct
 

Not sure how to implement your suggestion. I tried to replace the last
argument in my original formula with your suggestion and it returned 0
(zero). Is this correct ?

"David Billigmeier" wrote:

First, I don't know why it's even letting you commit that formula with
semi-colon's to separate each condition. SUMPRODUCT is set up to allow
comma's for the breaks, so you might want to change that.

Second, the way you have the formula set up now, it sums Sheet1!$N$4:$N$903
if all 4 conditions are met (which is why it's returning 0, because there are
no numerical values). What are you actually trying to do with the values in
column N? You need to specifially set a condition for what you are trying to
count. For example, if you are trying to count the number of non-missing
values in column N when all 4 conditions are met, change to this:

--(Sheet1!$N$4:$N$903<"")

Does that make sense?
--
Regards,
Dave


"carl" wrote:

I use this formula:

=SUMPRODUCT(--(Sheet1!$Y$4:$Y$903=Sheet2!G$10);--(Sheet1!$AB$4:$AB$903="Directed");--(Sheet1!$I$4:$I$903=Sheet2!E11);--(Sheet1!$AC$4:$AC$903=60);Sheet1!$N$4:$N$903)

The values in Col N are not numbers. The formula returns 0 (zero).

Is there a way to modify the formula to count the number of cells that
satisfy the 4 criteria ?

Thank you in advance.

Sumproduct
 

To count the unique values in Column N where Columns I, Y, AB, and AC
meet your criteria, try...

=SUM(IF(FREQUENCY(IF((Sheet1!$I$4:$I$903=Sheet2!E1 1)*(Sheet1!$Y$4:$Y$903=
Sheet2!G$10)*(Sheet1!$AB$4:$AB$903="Directed")*(Sh eet1!$AC$4:$AC$903=60)*
(Sheet1!$N$4:$N$903<""),MATCH(Sheet1!$N$4:$N$903, Sheet1!$N$4:$N$903,0)),
ROW(Sheet1!$N$4:$N$903)-ROW(Sheet1!$N$4)+1)0,1))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
"carl" wrote:

Thank you. It does make sense.

Any chance I can set the criteria --(Sheet1!$N$4:$N$903<"") to only count
Unique Values ?

"David Billigmeier" wrote:

First, I don't know why it's even letting you commit that formula with
semi-colon's to separate each condition. SUMPRODUCT is set up to allow
comma's for the breaks, so you might want to change that.

Second, the way you have the formula set up now, it sums Sheet1!$N$4:$N$903
if all 4 conditions are met (which is why it's returning 0, because there
are
no numerical values). What are you actually trying to do with the values
in
column N? You need to specifially set a condition for what you are trying
to
count. For example, if you are trying to count the number of non-missing
values in column N when all 4 conditions are met, change to this:

--(Sheet1!$N$4:$N$903<"")

Does that make sense?
--
Regards,
Dave


"carl" wrote:

I use this formula:

=SUMPRODUCT(--(Sheet1!$Y$4:$Y$903=Sheet2!G$10);--(Sheet1!$AB$4:$AB$903="Di
rected");--(Sheet1!$I$4:$I$903=Sheet2!E11);--(Sheet1!$AC$4:$AC$903=60);She
et1!$N$4:$N$903)

The values in Col N are not numbers. The formula returns 0 (zero).

Is there a way to modify the formula to count the number of cells that
satisfy the 4 criteria ?

Thank you in advance.