Counting or Total Formulas -- Further Explanation

[MAB]

I put in a question about this the last couple days, and I got some answers,
but due to my poor explanation, the info provided didn't work.

I'll try again...

I would like to know if the following is possible:

1) Column N (N4:N8): This should show individual totals for various PC
location codes found in column F that corresponds with an N/A in the same row
from over in column A. (PC location codes are C, S, H, F, & O -- so all N/A's
for C, all N/A's for S, etc).

To confirm, I want to compare the codes in column F against data in column
A, and count each N/A encountered, for each separate code.

Per a previous suggestion, I tried using
=SUMPRODUCT((A1:A1000="C"),(F1:F1000)="N/A"), but it didn't work. I
shouldn't say they didn't work, but the results all come back as "0" and I
know none of the results should equal zero.

2) Column O (O4:O8): This should show individual totals for various PC
location codes found in column F that are > 5000, but <=50000. (PC location
codes are C, S, H, F, & O -- so all instances of > 5000, but <=50000 for C,
all instances of > 5000, but <=50000 for S, etc).

For this I tried using
=SUMPRODUCT((A1:A1000="C"),(F1:F1000)>5000,(F1:F1000)<=50000), but as in the
case above I got back "0" as my results and I shouldn't have.

3) Column P (P48): This should show individual totals for various PC
location codes found in column F that are > 50000. (PC location codes are C,
S, H, F, & O -- so all instances of > 50000 for C, all instances of > 50000
for S, etc).

I didn't know what to do for this one.

Thanks in advance for everyone's help. I normally wouldn't take up this
much space, but I my poor explanations of what I'm hoping to use caused the
multiple posts.

MAB

 

[Bob Phillips]

I put in a question about this the last couple days, and I got some answers,
but due to my poor explanation, the info provided didn't work.

I'll try again...

I would like to know if the following is possible:

1) Column N (N4:N8): This should show individual totals for various PC
location codes found in column F that corresponds with an N/A in the same row
from over in column A. (PC location codes are C, S, H, F, & O -- so all N/A's
for C, all N/A's for S, etc).

To confirm, I want to compare the codes in column F against data in column
A, and count each N/A encountered, for each separate code.

Per a previous suggestion, I tried using
=SUMPRODUCT((A1:A1000="C"),(F1:F1000)="N/A"), but it didn't work. I
shouldn't say they didn't work, but the results all come back as "0" and I
know none of the results should equal zero.

The formula should be

=SUMPRODUCT(--(A1:A1000="C"),--(F1:F1000="N/A"))

and this assumes a text of N/A, not a #N/A as a result of a formula.

2) Column O (O4:O8): This should show individual totals for various PC
location codes found in column F that are > 5000, but <=50000. (PC location
codes are C, S, H, F, & O -- so all instances of > 5000, but <=50000 for C,
all instances of > 5000, but <=50000 for S, etc).

For this I tried using
=SUMPRODUCT((A1:A1000="C"),(F1:F1000)>5000,(F1:F1000)<=50000), but as in the
case above I got back "0" as my results and I shouldn't have.

Syntax again

=SUMPRODUCT(--($A$1:$A$1000="C"),--($F$1:$F$1000>5000),--($F$1:$F$1000<=5000
0))

3) Column P (P48): This should show individual totals for various PC
location codes found in column F that are > 50000. (PC location codes are C,
S, H, F, & O -- so all instances of > 50000 for C, all instances of > 50000
for S, etc).

Is that not simply


=SUMPRODUCT(--($A$1:$A$1000="C"),--($F$1:$F$1000>5000))

 

[MAB]

Woohoo!

They worked. Thank you very much.

Have a great day!

MAB

 

[Bob Phillips]

I will, and you have one too <G>

Bob

Woohoo!

They worked. Thank you very much.

Have a great day!

MAB