|
|
|
|
|
|
|
|
|
|
| |
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. |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
|
|
| |
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. |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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
"ShaneDevenshire" wrote:
> 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. |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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)
--
Biff
Microsoft Excel MVP
"DevinMaec" <DevinMaec@discussions.microsoft.com> wrote in message
news:9B531A72-2B2C-425D-80A9-598681E0B84B@microsoft.com...
> 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
>
> "ShaneDevenshire" wrote:
>
>> 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.
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
Question
Answer
Notify me of replies