Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Count help
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=378220 |
#5
|
|||
|
|||
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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Match Last Occurrence of two numbers and Count to Previous Occurence | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
Count number to reach a cumulative value | Excel Worksheet Functions |