Countif Problem

[Wally Steadman]

Greetings all,
I have a spreadsheet with two worksheets. First worksheet is a weekly
calendar type layout example below:
Range B2 through f8 is a named range called workweek

B C D
E F
Monday Tuesday Wednesday
Thursday Friday
2 8:00 A1 B1 B2
A2 A1, A2
3 8:15 A2 A1 B2
A2, A3 A1
4 8:30
5 8:45
6 9:00
7 9:15
8 9:30


Second table is all the codes like A1 and B1
Next to each code I did an =countif(sheet1!workweek, "A1")
where each code is based on the code it represents.

So my answer block would look like below

A1 3
A2 2
A3
B1 1
B2 2

noticing how it only counted A1 3 times but there are actually 4 A1 entries.
I am trying to figure out how to get it to look at the entire cell and if it
contains an A1 then count that as well. So in example above, on Friday at
8:00 it should count 1 for the A1 and 1 for A2, but I am not sure how to get
it to do this. Any help would be appreciated. Thanks so much

Wally Steadman

[Peo Sjoblom]

=countif(sheet1!workweek, "*A1*")

you can use wildcards

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Wally Steadman" <[email protected]> wrote in message
news:[email protected]...
> Greetings all,
> I have a spreadsheet with two worksheets. First worksheet is a weekly
> calendar type layout example below:
> Range B2 through f8 is a named range called workweek
>
> B C D
> E F
> Monday Tuesday Wednesday Thursday
> Friday
> 2 8:00 A1 B1 B2 A2
> A1, A2
> 3 8:15 A2 A1 B2
> A2, A3 A1
> 4 8:30
> 5 8:45
> 6 9:00
> 7 9:15
> 8 9:30
>
>
> Second table is all the codes like A1 and B1
> Next to each code I did an =countif(sheet1!workweek, "A1")
> where each code is based on the code it represents.
>
> So my answer block would look like below
>
> A1 3
> A2 2
> A3
> B1 1
> B2 2
>
> noticing how it only counted A1 3 times but there are actually 4 A1
> entries. I am trying to figure out how to get it to look at the entire
> cell and if it contains an A1 then count that as well. So in example
> above, on Friday at 8:00 it should count 1 for the A1 and 1 for A2, but I
> am not sure how to get it to do this. Any help would be appreciated.
> Thanks so much
>
> Wally Steadman
>

[Wally Steadman]

Thanks for that help it works but bring up another problem for me and I know
there is an answer but for the life of me I can't get my mind wrapped around
it.

If I say
=Countif(workweek, "*A1*") it works but is also counts A10 when what I need
it to do is only count A1

Cell 1 A1, B2
Cell 2 A1
Cell 3 A10, B1

I would need it to show A1 was only counted 2 times, A10 Counted 1 time, B1
counted 1 Time and B2 counted 1 time. Is there a way I can say *A1#* where
# equals only a space or a comma or something like that?


"Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
news:[email protected]...
> =countif(sheet1!workweek, "*A1*")
>
> you can use wildcards
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> Excel 95 - Excel 2007
> Northwest Excel Solutions
> www.nwexcelsolutions.com
> "It is a good thing to follow the first law of holes;
> if you are in one stop digging." Lord Healey
>
>
> "Wally Steadman" <[email protected]> wrote in message
> news:[email protected]...
>> Greetings all,
>> I have a spreadsheet with two worksheets. First worksheet is a weekly
>> calendar type layout example below:
>> Range B2 through f8 is a named range called workweek
>>
>> B C
>> D E F
>> Monday Tuesday Wednesday
>> Thursday Friday
>> 2 8:00 A1 B1 B2
>> A2 A1, A2
>> 3 8:15 A2 A1 B2
>> A2, A3 A1
>> 4 8:30
>> 5 8:45
>> 6 9:00
>> 7 9:15
>> 8 9:30
>>
>>
>> Second table is all the codes like A1 and B1
>> Next to each code I did an =countif(sheet1!workweek, "A1")
>> where each code is based on the code it represents.
>>
>> So my answer block would look like below
>>
>> A1 3
>> A2 2
>> A3
>> B1 1
>> B2 2
>>
>> noticing how it only counted A1 3 times but there are actually 4 A1
>> entries. I am trying to figure out how to get it to look at the entire
>> cell and if it contains an A1 then count that as well. So in example
>> above, on Friday at 8:00 it should count 1 for the A1 and 1 for A2, but I
>> am not sure how to get it to do this. Any help would be appreciated.
>> Thanks so much
>>
>> Wally Steadman
>>
>
>

[Paul Mathews]

Wally, if you have some certainty that your list separators will always be
commas (in your example below, the Thursday entry is A1, A2), you could do
this:

=Countif(Workweek, "A1") + Countif(Workweek, "A1,")

So this catches all single entries of A1 (or list entries where A1 is the
last item, e.g., "B1, A1") and list entries where A1 is not the last item
("A1, A2").

"Wally Steadman" wrote:

> Thanks for that help it works but bring up another problem for me and I know
> there is an answer but for the life of me I can't get my mind wrapped around
> it.
>
> If I say
> =Countif(workweek, "*A1*") it works but is also counts A10 when what I need
> it to do is only count A1
>
> Cell 1 A1, B2
> Cell 2 A1
> Cell 3 A10, B1
>
> I would need it to show A1 was only counted 2 times, A10 Counted 1 time, B1
> counted 1 Time and B2 counted 1 time. Is there a way I can say *A1#* where
> # equals only a space or a comma or something like that?
>
>
> "Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
> news:[email protected]...
> > =countif(sheet1!workweek, "*A1*")
> >
> > you can use wildcards
> >
> > --
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> > Excel 95 - Excel 2007
> > Northwest Excel Solutions
> > www.nwexcelsolutions.com
> > "It is a good thing to follow the first law of holes;
> > if you are in one stop digging." Lord Healey
> >
> >
> > "Wally Steadman" <[email protected]> wrote in message
> > news:[email protected]...
> >> Greetings all,
> >> I have a spreadsheet with two worksheets. First worksheet is a weekly
> >> calendar type layout example below:
> >> Range B2 through f8 is a named range called workweek
> >>
> >> B C
> >> D E F
> >> Monday Tuesday Wednesday
> >> Thursday Friday
> >> 2 8:00 A1 B1 B2
> >> A2 A1, A2
> >> 3 8:15 A2 A1 B2
> >> A2, A3 A1
> >> 4 8:30
> >> 5 8:45
> >> 6 9:00
> >> 7 9:15
> >> 8 9:30
> >>
> >>
> >> Second table is all the codes like A1 and B1
> >> Next to each code I did an =countif(sheet1!workweek, "A1")
> >> where each code is based on the code it represents.
> >>
> >> So my answer block would look like below
> >>
> >> A1 3
> >> A2 2
> >> A3
> >> B1 1
> >> B2 2
> >>
> >> noticing how it only counted A1 3 times but there are actually 4 A1
> >> entries. I am trying to figure out how to get it to look at the entire
> >> cell and if it contains an A1 then count that as well. So in example
> >> above, on Friday at 8:00 it should count 1 for the A1 and 1 for A2, but I
> >> am not sure how to get it to do this. Any help would be appreciated.
> >> Thanks so much
> >>
> >> Wally Steadman
> >>
> >
> >
>
>
>

[daddylonglegs]

If all entries in a cell except the last are followed by a comma

=SUMPRODUCT(--ISNUMBER(SEARCH("A1,",workweek&",")))

[Paul Mathews]

Whoops, sorry, I meant to say:

=Countif(Workweek, "*A1") + Countif(Workweek, "*A1,*")

"Wally Steadman" wrote:

> Thanks for that help it works but bring up another problem for me and I know
> there is an answer but for the life of me I can't get my mind wrapped around
> it.
>
> If I say
> =Countif(workweek, "*A1*") it works but is also counts A10 when what I need
> it to do is only count A1
>
> Cell 1 A1, B2
> Cell 2 A1
> Cell 3 A10, B1
>
> I would need it to show A1 was only counted 2 times, A10 Counted 1 time, B1
> counted 1 Time and B2 counted 1 time. Is there a way I can say *A1#* where
> # equals only a space or a comma or something like that?
>
>
> "Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
> news:[email protected]...
> > =countif(sheet1!workweek, "*A1*")
> >
> > you can use wildcards
> >
> > --
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> > Excel 95 - Excel 2007
> > Northwest Excel Solutions
> > www.nwexcelsolutions.com
> > "It is a good thing to follow the first law of holes;
> > if you are in one stop digging." Lord Healey
> >
> >
> > "Wally Steadman" <[email protected]> wrote in message
> > news:[email protected]...
> >> Greetings all,
> >> I have a spreadsheet with two worksheets. First worksheet is a weekly
> >> calendar type layout example below:
> >> Range B2 through f8 is a named range called workweek
> >>
> >> B C
> >> D E F
> >> Monday Tuesday Wednesday
> >> Thursday Friday
> >> 2 8:00 A1 B1 B2
> >> A2 A1, A2
> >> 3 8:15 A2 A1 B2
> >> A2, A3 A1
> >> 4 8:30
> >> 5 8:45
> >> 6 9:00
> >> 7 9:15
> >> 8 9:30
> >>
> >>
> >> Second table is all the codes like A1 and B1
> >> Next to each code I did an =countif(sheet1!workweek, "A1")
> >> where each code is based on the code it represents.
> >>
> >> So my answer block would look like below
> >>
> >> A1 3
> >> A2 2
> >> A3
> >> B1 1
> >> B2 2
> >>
> >> noticing how it only counted A1 3 times but there are actually 4 A1
> >> entries. I am trying to figure out how to get it to look at the entire
> >> cell and if it contains an A1 then count that as well. So in example
> >> above, on Friday at 8:00 it should count 1 for the A1 and 1 for A2, but I
> >> am not sure how to get it to do this. Any help would be appreciated.
> >> Thanks so much
> >>
> >> Wally Steadman
> >>
> >
> >
>
>
>