Count blank with multiple criteria

[Guest]

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((D2350=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.

 

[T. Valko]

Try this:

=SUMPRODUCT(--(D210=""),--(LEFT(F2:F10)="A"),--(LEFT(G2:G10)="C"))

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

Yes. It means to test the cells in the range and see if the first character
in the cell is an "A". (not case sensitive).

contains item numbers commencing with A

I'm assuming that means you have alpha numeric codes like:

Axc110
All214
Bbv999
Trx001

And you want to count those that start with "A".

Biff

 

[Guest]

YES! YES! YES! Thanks heaps.

Try this:

=SUMPRODUCT(--(D210=""),--(LEFT(F2:F10)="A"),--(LEFT(G2:G10)="C"))


Yes. It means to test the cells in the range and see if the first character
in the cell is an "A". (not case sensitive).


I'm assuming that means you have alpha numeric codes like:

Axc110
All214
Bbv999
Trx001

And you want to count those that start with "A".

Biff

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

YES! YES! YES! Thanks heaps.