Counting or Total Formulas -- Further Explanation

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 (P4:P8): 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. :-p

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

"MAB" <[email protected]> wrote in message
news:[email protected]...
> 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 (P4:P8): 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))

Woohoo!

They worked. Thank you very much.

Have a great day!

MAB

"Bob Phillips" wrote:

>
>
> "MAB" <[email protected]> wrote in message
> news:[email protected]...
> > 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 (P4:P8): 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))
>
>
>

I will, and you have one too <G>

Bob

"MAB" <[email protected]> wrote in message
news:[email protected]...
> Woohoo!
>
> They worked. Thank you very much.
>
> Have a great day!
>
> MAB
>
> "Bob Phillips" wrote:
>
> >
> >
> > "MAB" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 (P4:P8): 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))
> >
> >
> >