Count help

[WOLLAM]

Not sure if this is the correct forum but I'll give a shot. Is there a
function in Excel to count if a value appears in a cell, for example, 3 times
in succession? For example:

Accepted Bid
Tom Y Y N Y Y Y N N N N Y Y Y N Y Y Y Y Y
John Y Y Y Y N N N Y N N N N N Y Y Y N N N

Is there a function that will tell me how many times Tom or John accepted or
refused a bid, on successive days? I am guessing it will have something to
do with the 'Count' function but have been unable to find an answer in Excel
help. In the above example John accepted 3 times in row twice, 5 times in a
row once, refused 4 times in a row once. Tom accepted 3x once, 4x once, and
refused 3x twice, 5x once.

Any help would be greatly appreciated.

[nuver]

Hello
Use the countif function. You will want to select your range and then in the criteria box enter "Y" if you want to count the number of times the bid was accepted.

[WOLLAM]

I think a better way to consider what I'm looking for might be: how many
times was a bid accepted three consecutive days (or four days, five days or
'x' number of days). The countif does help though, thank you.

"nuver" wrote:

>
> Hello
> Use the countif function. You will want to select your range and then
> in the criteria box enter "Y" if you want to count the number of times
> the bid was accepted.
>
>
> --
> nuver
> ------------------------------------------------------------------------
> nuver's Profile: http://www.excelforum.com/member.php...o&userid=10036
> View this thread: http://www.excelforum.com/showthread...hreadid=378220
>
>

[mangesh_yadav]

Suppose the series of Ns and Ys is entered in the range B1:T1 (as er your example), then to count 3 Ys, use the formula:

=FIND(("N"&REPT("Y",3)&"N"),(B1&C1&D1&E1&F1&G1&H1&I1&J1&K1&L1&M1&N1&O1&P1&Q1&R1&S1&T1))


Mangesh

[Biff]

Hi!

Try this:

For "Tom".....

A1 = Tom
B1:T1 = Y Y N Y Y Y N N N N Y Y Y N Y Y Y Y Y

Enter this formula in B2:

=IF(C1<>B1,1,"")

Enter this formula in C2 and copy across to T2:

=IF(D1<>C1,COLUMNS($B1:C1)-IF(COUNT($B2:B2),LOOKUP(2,1/ISNUMBER($B2:B2),COLUMN($B1:B1)-1),0),"")

To count the number of times Tom said NO twice (or, whatever):

=SUMPRODUCT(--($B$1:$T$1="N"),--($B$2:$T$2=2))

To count the numbr of times Tom said YES 3 times (or, whatever):

=SUMPRODUCT(--($B$1:$T$1="Y"),--($B$2:$T$2=3))

>In the above example John accepted 3 times in row twice, 5 times in a
> row once, refused 4 times in a row once. Tom accepted 3x once, 4x once,
> and
> refused 3x twice, 5x once.

I think you have some of these backwards.

Biff

"WOLLAM" <[email protected]> wrote in message
news:[email protected]...
> Not sure if this is the correct forum but I'll give a shot. Is there a
> function in Excel to count if a value appears in a cell, for example, 3
> times
> in succession? For example:
>
> Accepted Bid
> Tom Y Y N Y Y Y N N N N Y Y Y N Y Y Y Y Y
> John Y Y Y Y N N N Y N N N N N Y Y Y N N N
>
> Is there a function that will tell me how many times Tom or John accepted
> or
> refused a bid, on successive days? I am guessing it will have something
> to
> do with the 'Count' function but have been unable to find an answer in
> Excel
> help. In the above example John accepted 3 times in row twice, 5 times in
> a
> row once, refused 4 times in a row once. Tom accepted 3x once, 4x once,
> and
> refused 3x twice, 5x once.
>
> Any help would be greatly appreciated.