Count with multiple criteria based on cell not blank?
 

I would greatley appreciate assistance with how to count the following:
If range d2:d350 contains varied account numbers, and range f2:f350 contain
a variety of system types (all starting with A), how do I total the number of
systems (all 'A' varieties) that belong to each account. I guess counting
the number of unblank cells in f2:f350 would do it, but can't work out the
correct formula.
I then need to count how many of the range D do not have a reference
allocated.

Count with multiple criteria based on cell not blank?
 

Hi,

If I understand correctly the following formula will do it:

=SUMPRODUCT((D2:D350=G2)*(LEFT(F2:F350)="A"))

Where cell G2 contains the account numbers.

The second part of the question seems to be asking something different: "I
then need to count how ... don't have a reference allocated" If you are
trying to find how may blank cells there are in column D you would use
=COUNTBLANK(D2:D350)

--
Thanks,
Shane Devenshire


"Twishlist" wrote:

I would greatley appreciate assistance with how to count the following:
If range d2:d350 contains varied account numbers, and range f2:f350 contain
a variety of system types (all starting with A), how do I total the number of
systems (all 'A' varieties) that belong to each account. I guess counting
the number of unblank cells in f2:f350 would do it, but can't work out the
correct formula.
I then need to count how many of the range D do not have a reference
allocated.

Count with multiple criteria based on cell not blank?
 

Thanks so much, Shane...it works! I will clarify the second part of my
question. I should have indicated that I have a list of sites (a2:a350) with
more than one system each, serviced by an account number (d2:d350).
Accordingly, I have two columns of sytems IDs, one contains item numbers
commencing with A (f2:f350) & another with items commencing with C(g2:g350).
I've used your formula in both columns.
But there remains the need to ascertain the number of systems (in either
column) that don't have an account reference(d2:d350).
Secondly, so I may understand your formula, would you please advise what
'left' refers to?
Also, does "A" collect anything in a column that starts with A on all
occasions, or just within this formula?
Thanks, in anticipation.

"ShaneDevenshire" wrote:

Hi,

If I understand correctly the following formula will do it:

=SUMPRODUCT((D2:D350=G2)*(LEFT(F2:F350)="A"))

Where cell G2 contains the account numbers.

The second part of the question seems to be asking something different: "I
then need to count how ... don't have a reference allocated" If you are
trying to find how may blank cells there are in column D you would use
=COUNTBLANK(D2:D350)

--
Thanks,
Shane Devenshire


"Twishlist" wrote:

I would greatley appreciate assistance with how to count the following:
If range d2:d350 contains varied account numbers, and range f2:f350 contain
a variety of system types (all starting with A), how do I total the number of
systems (all 'A' varieties) that belong to each account. I guess counting
the number of unblank cells in f2:f350 would do it, but can't work out the
correct formula.
I then need to count how many of the range D do not have a reference
allocated.

Count with multiple criteria based on cell not blank?
 

Hi,

First the LEFT - because the default second argument of the left function is
1 I left it out, I could have written it
=SUMPRODUCT((D2:D350=G2)*(LEFT(F2:F350,1)="A")) which checks each item in
column F to see if it starts with A.

Second, if all cells in columns C and F had something in them then all you
would need to do is count the number of blanks in column D. However, if
columns C & F can be empty then the formula for checking for blanks could be:

=SUMPRODUCT((F2:F350<"")*(D2:D350=""))

and a similar one for column C.

--
Cheers,
Shane Devenshire


"Twishlist" wrote:

Thanks so much, Shane...it works! I will clarify the second part of my
question. I should have indicated that I have a list of sites (a2:a350) with
more than one system each, serviced by an account number (d2:d350).
Accordingly, I have two columns of sytems IDs, one contains item numbers
commencing with A (f2:f350) & another with items commencing with C(g2:g350).
I've used your formula in both columns.
But there remains the need to ascertain the number of systems (in either
column) that don't have an account reference(d2:d350).
Secondly, so I may understand your formula, would you please advise what
'left' refers to?
Also, does "A" collect anything in a column that starts with A on all
occasions, or just within this formula?
Thanks, in anticipation.

"ShaneDevenshire" wrote:

Hi,

If I understand correctly the following formula will do it:

=SUMPRODUCT((D2:D350=G2)*(LEFT(F2:F350)="A"))

Where cell G2 contains the account numbers.

The second part of the question seems to be asking something different: "I
then need to count how ... don't have a reference allocated" If you are
trying to find how may blank cells there are in column D you would use
=COUNTBLANK(D2:D350)

--
Thanks,
Shane Devenshire


"Twishlist" wrote:

I would greatley appreciate assistance with how to count the following:
If range d2:d350 contains varied account numbers, and range f2:f350 contain
a variety of system types (all starting with A), how do I total the number of
systems (all 'A' varieties) that belong to each account. I guess counting
the number of unblank cells in f2:f350 would do it, but can't work out the
correct formula.
I then need to count how many of the range D do not have a reference
allocated.

Count with multiple criteria based on cell not blank?
 

Dear ShaneDevinshire

I have E15:E800 with 9 diffrent MD names that are repeted at random.
i want to total how many times the diffrent MD names appear in column E into
9 diffrent cells one cell for each MD name. I cannot figure this out. can you
help?

Thank you
Devin

"ShaneDevenshire" wrote:

Hi,

First the LEFT - because the default second argument of the left function is
1 I left it out, I could have written it
=SUMPRODUCT((D2:D350=G2)*(LEFT(F2:F350,1)="A")) which checks each item in
column F to see if it starts with A.

Second, if all cells in columns C and F had something in them then all you
would need to do is count the number of blanks in column D. However, if
columns C & F can be empty then the formula for checking for blanks could be:

=SUMPRODUCT((F2:F350<"")*(D2:D350=""))

and a similar one for column C.

--
Cheers,
Shane Devenshire


"Twishlist" wrote:

Thanks so much, Shane...it works! I will clarify the second part of my
question. I should have indicated that I have a list of sites (a2:a350) with
more than one system each, serviced by an account number (d2:d350).
Accordingly, I have two columns of sytems IDs, one contains item numbers
commencing with A (f2:f350) & another with items commencing with C(g2:g350).
I've used your formula in both columns.
But there remains the need to ascertain the number of systems (in either
column) that don't have an account reference(d2:d350).
Secondly, so I may understand your formula, would you please advise what
'left' refers to?
Also, does "A" collect anything in a column that starts with A on all
occasions, or just within this formula?
Thanks, in anticipation.

"ShaneDevenshire" wrote:

Hi,

If I understand correctly the following formula will do it:

=SUMPRODUCT((D2:D350=G2)*(LEFT(F2:F350)="A"))

Where cell G2 contains the account numbers.

The second part of the question seems to be asking something different: "I
then need to count how ... don't have a reference allocated" If you are
trying to find how may blank cells there are in column D you would use
=COUNTBLANK(D2:D350)

--
Thanks,
Shane Devenshire


"Twishlist" wrote:

I would greatley appreciate assistance with how to count the following:
If range d2:d350 contains varied account numbers, and range f2:f350 contain
a variety of system types (all starting with A), how do I total the number of
systems (all 'A' varieties) that belong to each account. I guess counting
the number of unblank cells in f2:f350 would do it, but can't work out the
correct formula.
I then need to count how many of the range D do not have a reference
allocated.

Count with multiple criteria based on cell not blank?
 

List the 9 unique MD names in a range of cells, say, G1:G9. Then enter this
formula in H1 and copy down to H9:

=COUNTIF(E$15:E$800,G1)

--
Biff
Microsoft Excel MVP


"DevinMaec" wrote in message
...
Dear ShaneDevinshire

I have E15:E800 with 9 diffrent MD names that are repeted at random.
i want to total how many times the diffrent MD names appear in column E
into
9 diffrent cells one cell for each MD name. I cannot figure this out. can
you
help?

Thank you
Devin

"ShaneDevenshire" wrote:

Hi,

First the LEFT - because the default second argument of the left function
is
1 I left it out, I could have written it
=SUMPRODUCT((D2:D350=G2)*(LEFT(F2:F350,1)="A")) which checks each item
in
column F to see if it starts with A.

Second, if all cells in columns C and F had something in them then all
you
would need to do is count the number of blanks in column D. However, if
columns C & F can be empty then the formula for checking for blanks could
be:

=SUMPRODUCT((F2:F350<"")*(D2:D350=""))

and a similar one for column C.

--
Cheers,
Shane Devenshire


"Twishlist" wrote:

Thanks so much, Shane...it works! I will clarify the second part of my
question. I should have indicated that I have a list of sites (a2:a350)
with
more than one system each, serviced by an account number (d2:d350).
Accordingly, I have two columns of sytems IDs, one contains item
numbers
commencing with A (f2:f350) & another with items commencing with
C(g2:g350).
I've used your formula in both columns.
But there remains the need to ascertain the number of systems (in
either
column) that don't have an account reference(d2:d350).
Secondly, so I may understand your formula, would you please advise
what
'left' refers to?
Also, does "A" collect anything in a column that starts with A on all
occasions, or just within this formula?
Thanks, in anticipation.

"ShaneDevenshire" wrote:

Hi,

If I understand correctly the following formula will do it:

=SUMPRODUCT((D2:D350=G2)*(LEFT(F2:F350)="A"))

Where cell G2 contains the account numbers.

The second part of the question seems to be asking something
different: "I
then need to count how ... don't have a reference allocated" If you
are
trying to find how may blank cells there are in column D you would
use
=COUNTBLANK(D2:D350)

--
Thanks,
Shane Devenshire


"Twishlist" wrote:

I would greatley appreciate assistance with how to count the
following:
If range d2:d350 contains varied account numbers, and range f2:f350
contain
a variety of system types (all starting with A), how do I total the
number of
systems (all 'A' varieties) that belong to each account. I guess
counting
the number of unblank cells in f2:f350 would do it, but can't work
out the
correct formula.
I then need to count how many of the range D do not have a
reference
allocated.

Count with multiple criteria based on cell not blank?
 

I have three priorities (1, 2 or 3) in a range of cells, f17:f82. In range
g17:g82, I have completion dates. How do I count the number of priority 1's
with no completion dates and record that value in h9?

"T. Valko" wrote:

List the 9 unique MD names in a range of cells, say, G1:G9. Then enter this
formula in H1 and copy down to H9:

=COUNTIF(E$15:E$800,G1)

--
Biff
Microsoft Excel MVP


"DevinMaec" wrote in message
...
Dear ShaneDevinshire

I have E15:E800 with 9 diffrent MD names that are repeted at random.
i want to total how many times the diffrent MD names appear in column E
into
9 diffrent cells one cell for each MD name. I cannot figure this out. can
you
help?

Thank you
Devin

"ShaneDevenshire" wrote:

Hi,

First the LEFT - because the default second argument of the left function
is
1 I left it out, I could have written it
=SUMPRODUCT((D2:D350=G2)*(LEFT(F2:F350,1)="A")) which checks each item
in
column F to see if it starts with A.

Second, if all cells in columns C and F had something in them then all
you
would need to do is count the number of blanks in column D. However, if
columns C & F can be empty then the formula for checking for blanks could
be:

=SUMPRODUCT((F2:F350<"")*(D2:D350=""))

and a similar one for column C.

--
Cheers,
Shane Devenshire


"Twishlist" wrote:

Thanks so much, Shane...it works! I will clarify the second part of my
question. I should have indicated that I have a list of sites (a2:a350)
with
more than one system each, serviced by an account number (d2:d350).
Accordingly, I have two columns of sytems IDs, one contains item
numbers
commencing with A (f2:f350) & another with items commencing with
C(g2:g350).
I've used your formula in both columns.
But there remains the need to ascertain the number of systems (in
either
column) that don't have an account reference(d2:d350).
Secondly, so I may understand your formula, would you please advise
what
'left' refers to?
Also, does "A" collect anything in a column that starts with A on all
occasions, or just within this formula?
Thanks, in anticipation.

"ShaneDevenshire" wrote:

Hi,

If I understand correctly the following formula will do it:

=SUMPRODUCT((D2:D350=G2)*(LEFT(F2:F350)="A"))

Where cell G2 contains the account numbers.

The second part of the question seems to be asking something
different: "I
then need to count how ... don't have a reference allocated" If you
are
trying to find how may blank cells there are in column D you would
use
=COUNTBLANK(D2:D350)

--
Thanks,
Shane Devenshire


"Twishlist" wrote:

I would greatley appreciate assistance with how to count the
following:
If range d2:d350 contains varied account numbers, and range f2:f350
contain
a variety of system types (all starting with A), how do I total the
number of
systems (all 'A' varieties) that belong to each account. I guess
counting
the number of unblank cells in f2:f350 would do it, but can't work
out the
correct formula.
I then need to count how many of the range D do not have a
reference
allocated.

Count with multiple criteria based on cell not blank?
 

In H9 use:
=sumproduct(--(f17:f82=1),--(g17:g82=""))

In the future, start a new thread when you have a new topic. You'll get more
replies that way.

Regards,
Fred

"Beeman" wrote in message
...
I have three priorities (1, 2 or 3) in a range of cells, f17:f82. In range
g17:g82, I have completion dates. How do I count the number of priority
1's
with no completion dates and record that value in h9?

"T. Valko" wrote:

List the 9 unique MD names in a range of cells, say, G1:G9. Then enter
this
formula in H1 and copy down to H9:

=COUNTIF(E$15:E$800,G1)

--
Biff
Microsoft Excel MVP


"DevinMaec" wrote in message
...
Dear ShaneDevinshire

I have E15:E800 with 9 diffrent MD names that are repeted at random.
i want to total how many times the diffrent MD names appear in column E
into
9 diffrent cells one cell for each MD name. I cannot figure this out.
can
you
help?

Thank you
Devin

"ShaneDevenshire" wrote:

Hi,

First the LEFT - because the default second argument of the left
function
is
1 I left it out, I could have written it
=SUMPRODUCT((D2:D350=G2)*(LEFT(F2:F350,1)="A")) which checks each
item
in
column F to see if it starts with A.

Second, if all cells in columns C and F had something in them then all
you
would need to do is count the number of blanks in column D. However,
if
columns C & F can be empty then the formula for checking for blanks
could
be:

=SUMPRODUCT((F2:F350<"")*(D2:D350=""))

and a similar one for column C.

--
Cheers,
Shane Devenshire


"Twishlist" wrote:

Thanks so much, Shane...it works! I will clarify the second part of
my
question. I should have indicated that I have a list of sites
(a2:a350)
with
more than one system each, serviced by an account number (d2:d350).
Accordingly, I have two columns of sytems IDs, one contains item
numbers
commencing with A (f2:f350) & another with items commencing with
C(g2:g350).
I've used your formula in both columns.
But there remains the need to ascertain the number of systems (in
either
column) that don't have an account reference(d2:d350).
Secondly, so I may understand your formula, would you please advise
what
'left' refers to?
Also, does "A" collect anything in a column that starts with A on
all
occasions, or just within this formula?
Thanks, in anticipation.

"ShaneDevenshire" wrote:

Hi,

If I understand correctly the following formula will do it:

=SUMPRODUCT((D2:D350=G2)*(LEFT(F2:F350)="A"))

Where cell G2 contains the account numbers.

The second part of the question seems to be asking something
different: "I
then need to count how ... don't have a reference allocated" If
you
are
trying to find how may blank cells there are in column D you would
use
=COUNTBLANK(D2:D350)

--
Thanks,
Shane Devenshire


"Twishlist" wrote:

I would greatley appreciate assistance with how to count the
following:
If range d2:d350 contains varied account numbers, and range
f2:f350
contain
a variety of system types (all starting with A), how do I total
the
number of
systems (all 'A' varieties) that belong to each account. I
guess
counting
the number of unblank cells in f2:f350 would do it, but can't
work
out the
correct formula.
I then need to count how many of the range D do not have a
reference
allocated.