(Msg. 1) Posted: Wed Jun 24, 2009 3:22 am
Post subject: SUMPRODUCT on Altered Criteria - ERROR Archived from groups: microsoft>public>excel>crashesgpfs (more info?)
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 “…”.
(Msg. 2) Posted: Wed Jun 24, 2009 4:39 am
Post subject: RE: SUMPRODUCT on Altered Criteria - ERROR [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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:
> 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
>
(Msg. 3) Posted: Wed Jun 24, 2009 4:41 am
Post subject: RE: SUMPRODUCT on Altered Criteria - ERROR [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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.
"EricB" wrote:
> 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
>
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office