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.

Try this:

=SUMPRODUCT(--(D2:D10=""),--(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

"Twishlist" wrote in message
...
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.

YES! YES! YES! Thanks heaps.

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(D2:D10=""),--(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

"Twishlist" wrote in message
...
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.

You're welcome. Thanks for the feedback!

Biff

"Twishlist" wrote in message
...
YES! YES! YES! Thanks heaps.

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(D2:D10=""),--(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

"Twishlist" wrote in message
...
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.