# Counting or Total Formulas -- Further Explanation

1. ## Counting or Total Formulas -- Further Explanation

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

2. ## Re: Counting or Total Formulas -- Further Explanation

"MAB" <MAB@discussions.microsoft.com> wrote in message
news:5468F867-7F4D-47E8-BF8D-F4F14C208E51@microsoft.com...

> 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))

3. ## Re: Counting or Total Formulas -- Further Explanation

Woohoo!

They worked. Thank you very much.

Have a great day!

MAB

"Bob Phillips" wrote:

>
>
> "MAB" <MAB@discussions.microsoft.com> wrote in message
> news:5468F867-7F4D-47E8-BF8D-F4F14C208E51@microsoft.com...
> > I put in a question about this the last couple days, and I got some

> > 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))
>
>
>

4. ## Re: Counting or Total Formulas -- Further Explanation

I will, and you have one too <G>

Bob

"MAB" <MAB@discussions.microsoft.com> wrote in message
news:4B23C35E-C933-4953-B5DA-289938F31C3E@microsoft.com...
> Woohoo!
>
> They worked. Thank you very much.
>
> Have a great day!
>
> MAB
>
> "Bob Phillips" wrote:
>
> >
> >
> > "MAB" <MAB@discussions.microsoft.com> wrote in message
> > news:5468F867-7F4D-47E8-BF8D-F4F14C208E51@microsoft.com...
> > > I put in a question about this the last couple days, and I got some

> > > 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))
> >
> >
> >

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1