SUMPRODUCT on Altered Criteria - ERROR

[EricB]

A X Y Z AA
1 Client1 I1 D0 B3 A1
2 Client2 I3 D1 B4 A2
3 Client3 I2 D3 B1 A0
4 Client4 I0 D0 B0 A1
5 Client5 I1 D1 B1 A2

Scenario:
1) I need to count the ‘clients’ in column A that have a value >Alpha0 (I
keep getting a formula error as soon as I use ‘ALPHA/NUMERIC’ or ‘>’ in my
formula. Though using COUNTIF: say ‘I0’ I get a result, as soon as I2
=COUNTIF(crq!$X$2:$X$65536,I2) I get a Zero result ??
2) Then SUMPRODUCT: I need to count from the list of data all clients where
the cell value is greater than ALPHA0 ie.
=SUMPRODUCT(--(crq!X2:X65536>="I1"),--(crq!Y2:Y65536>="D1"),--(crq!Z2:Z65536>="B1"),--(crq!AA2:AA65536>="A1"))
3) Even when I use data in X to AA as Alpa â€â€¦â€, I still get an #Value or
#NAME error, depending on me deleting or adding the “…â€.

Any assistance?

Regards

EricB

 

[Stefi]

1. What is Alpha0?
Show us your formula you use ‘ALPHA/NUMERIC’ or ‘>’ in!

Instead of =COUNTIF(crq!$X$2:$X$65536,I2) use
=COUNTIF(crq!$X$2:$X$65536,"I2") if it really refers to another sheet named
crq!

Your example data don't show a header in row1, so you should search in range
$X$1:$X$65536 or simply X:X!

2. Again: What is Alpha0?
3. Please, rephrase your question (possibly with example), it's not
understandable!

Regards,
Stefi

„EricB†ezt írta:

 

[EricB]

PS. Found one error but thought of another. One vale was not ALPHA but Numeric.
I however need to incorporate IF, OR into the formula. Thus to count any
value > 0 in the criteria given.