I have a list of sites (a2:a350), each with two columns of sytems IDs, one
contains item numbers commencing with A (f2:f350) & another with items
commencing with C(g2:g350). Each system is serviced by an account number
(d2:d350).
I've used Shane's formula, =SUMPRODUCT((D2:D350=G2)*(LEFT(F2:F350)="A")) to
ascertain how many sustems in each column belong to a particular account.
BUT, there remains the need to ascertain the number of systems (in either
column) that don't have an account reference allocated (d2:d350).
Secondly, so I may understand 'Shane's formula, 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.