Sumproduct

[Guest]

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.

 

[Guest]

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?

 

[Domenic]

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!

 

[Guest]

Thank you. It does make sense.

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

 

[Guest]

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 ?

 

[Domenic]

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!E11)*(Sheet1!$Y$4:$Y$903=
Sheet2!G$10)*(Sheet1!$AB$4:$AB$903="Directed")*(Sheet1!$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!