Count with multiple criteria based on cell not blank?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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 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.
 
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.
 
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
 
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)
 
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?
 
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
 
Back
Top