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.