Counting challenge with complex criteria

brystmar · Jul 9, 2015

Hi guys,
I'm usually able to find creative solutions in Excel, but this one had me stumped yesterday. I have a list of ~850 transactions, one per row, with the following details for each:

Transaction code
Status returned (Gold, Silver, Bronze, or Platinum)
Session start timestamp
Session finish timestamp

For each Gold status, I want to count the number of session starts the system saw since the last Gold session finished. [Grab the full data set from here.]

Excel 2012

A

B

C

D

E

1

TCode

Status

Start Timestamp

Finish Timestamp

# starts since the last Gold finish

2

N1HRRJ38BGMR79

Platinum

6/26/2015 15:21

6/26/2015 15:22

?

3

N1HRRLZN6P8L75

Silver

6/26/2015 15:25

6/26/2015 15:26

?

4

N1HRRN25DTHBTH

Silver

6/26/2015 15:27

?

5

N1HRRLYQGHSBZ3

Gold

6/26/2015 15:25

6/26/2015 15:31

?

6

N1HRRT82BK8959

Silver

6/26/2015 15:34

6/26/2015 15:35

?

7

N1HRRSTRHRTRBF

Silver

6/26/2015 15:33

6/26/2015 15:36

?

8

N1HRRQY9VK3XZF

Gold

6/26/2015 15:29

6/26/2015 15:39

?

9

N1HRRY97577LVK

Silver

6/26/2015 15:40

6/26/2015 15:41

?

10

N1HRRRW5X57YK8

Bronze

6/26/2015 15:31

6/26/2015 15:54

?

11

N1HRS8D9GG28MB

Silver

6/26/2015 15:57

?

12

N1HRSXPXHGZXNN

Gold

6/26/2015 16:13

6/26/2015 16:21

?

Sheet1

Sheet1

Below is the method I employed, which involves multiple formulas & iterations:

Excel 2012

F

G

H

I

J

K

1

Gold Finish

Prev Gold Finish

Iterate Row 2

Iterate Row 3

Iterate Row 4

Iterate Row 5

2

1/0/1900 0:00

F

T

3

1/0/1900 0:00

F

T

4

1/0/1900 0:00

F

5

6/26/2015 15:25

1/0/1900 0:00

F

6

1/0/1900 0:00

F

7

1/0/1900 0:00

F

8

6/26/2015 15:29

6/26/2015 15:25

F

9

1/0/1900 0:00

F

10

1/0/1900 0:00

F

11

1/0/1900 0:00

F

12

6/26/2015 16:13

6/26/2015 15:29

F

Sheet1

Sheet1 <table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" >Worksheet Formulas<table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H2</th><td style="text-align:left">=IF(AND($B2<>"Gold",$C2>=$G$2,$C2<=$F$2),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I2</th><td style="text-align:left">=IF(AND($B2<>"Gold",$C2>=$G$3,$C2<=$F$3),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J2</th><td style="text-align:left">=IF(AND($B2<>"Gold",$C2>=$G$4,$C2<=$F$4),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K2</th><td style="text-align:left">=IF(AND($B2<>"Gold",$C2>=$G$5,$C2<=$F$5),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F2</th><td style="text-align:left">=IF(B2="Gold",C2,0)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F3</th><td style="text-align:left">=IF(B3="Gold",C3,0)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F4</th><td style="text-align:left">=IF(B4="Gold",C4,0)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F5</th><td style="text-align:left">=IF(B5="Gold",C5,0)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F6</th><td style="text-align:left">=IF(B6="Gold",C6,0)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F7</th><td style="text-align:left">=IF(B7="Gold",C7,0)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F8</th><td style="text-align:left">=IF(B8="Gold",C8,0)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F9</th><td style="text-align:left">=IF(B9="Gold",C9,0)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F10</th><td style="text-align:left">=IF(B10="Gold",C10,0)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F11</th><td style="text-align:left">=IF(B11="Gold",C11,0)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F12</th><td style="text-align:left">=IF(B12="Gold",C12,0)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G3</th><td style="text-align:left">=IF(F3<>0,MAX(F$2:F2),F3)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H3</th><td style="text-align:left">=IF(AND($B3<>"Gold",$C3>=$G$2,$C3<=$F$2),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I3</th><td style="text-align:left">=IF(AND($B3<>"Gold",$C3>=$G$3,$C3<=$F$3),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J3</th><td style="text-align:left">=IF(AND($B3<>"Gold",$C3>=$G$4,$C3<=$F$4),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K3</th><td style="text-align:left">=IF(AND($B3<>"Gold",$C3>=$G$5,$C3<=$F$5),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G4</th><td style="text-align:left">=IF(F4<>0,MAX(F$2:F3),F4)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H4</th><td style="text-align:left">=IF(AND($B4<>"Gold",$C4>=$G$2,$C4<=$F$2),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I4</th><td style="text-align:left">=IF(AND($B4<>"Gold",$C4>=$G$3,$C4<=$F$3),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J4</th><td style="text-align:left">=IF(AND($B4<>"Gold",$C4>=$G$4,$C4<=$F$4),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K4</th><td style="text-align:left">=IF(AND($B4<>"Gold",$C4>=$G$5,$C4<=$F$5),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G5</th><td style="text-align:left">=IF(F5<>0,MAX(F$2:F4),F5)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H5</th><td style="text-align:left">=IF(AND($B5<>"Gold",$C5>=$G$2,$C5<=$F$2),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I5</th><td style="text-align:left">=IF(AND($B5<>"Gold",$C5>=$G$3,$C5<=$F$3),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J5</th><td style="text-align:left">=IF(AND($B5<>"Gold",$C5>=$G$4,$C5<=$F$4),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K5</th><td style="text-align:left">=IF(AND($B5<>"Gold",$C5>=$G$5,$C5<=$F$5),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G6</th><td style="text-align:left">=IF(F6<>0,MAX(F$2:F5),F6)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H6</th><td style="text-align:left">=IF(AND($B6<>"Gold",$C6>=$G$2,$C6<=$F$2),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I6</th><td style="text-align:left">=IF(AND($B6<>"Gold",$C6>=$G$3,$C6<=$F$3),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J6</th><td style="text-align:left">=IF(AND($B6<>"Gold",$C6>=$G$4,$C6<=$F$4),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K6</th><td style="text-align:left">=IF(AND($B6<>"Gold",$C6>=$G$5,$C6<=$F$5),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G7</th><td style="text-align:left">=IF(F7<>0,MAX(F$2:F6),F7)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H7</th><td style="text-align:left">=IF(AND($B7<>"Gold",$C7>=$G$2,$C7<=$F$2),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I7</th><td style="text-align:left">=IF(AND($B7<>"Gold",$C7>=$G$3,$C7<=$F$3),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J7</th><td style="text-align:left">=IF(AND($B7<>"Gold",$C7>=$G$4,$C7<=$F$4),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K7</th><td style="text-align:left">=IF(AND($B7<>"Gold",$C7>=$G$5,$C7<=$F$5),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G8</th><td style="text-align:left">=IF(F8<>0,MAX(F$2:F7),F8)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H8</th><td style="text-align:left">=IF(AND($B8<>"Gold",$C8>=$G$2,$C8<=$F$2),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I8</th><td style="text-align:left">=IF(AND($B8<>"Gold",$C8>=$G$3,$C8<=$F$3),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J8</th><td style="text-align:left">=IF(AND($B8<>"Gold",$C8>=$G$4,$C8<=$F$4),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K8</th><td style="text-align:left">=IF(AND($B8<>"Gold",$C8>=$G$5,$C8<=$F$5),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G9</th><td style="text-align:left">=IF(F9<>0,MAX(F$2:F8),F9)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H9</th><td style="text-align:left">=IF(AND($B9<>"Gold",$C9>=$G$2,$C9<=$F$2),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I9</th><td style="text-align:left">=IF(AND($B9<>"Gold",$C9>=$G$3,$C9<=$F$3),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J9</th><td style="text-align:left">=IF(AND($B9<>"Gold",$C9>=$G$4,$C9<=$F$4),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K9</th><td style="text-align:left">=IF(AND($B9<>"Gold",$C9>=$G$5,$C9<=$F$5),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G10</th><td style="text-align:left">=IF(F10<>0,MAX(F$2:F9),F10)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H10</th><td style="text-align:left">=IF(AND($B10<>"Gold",$C10>=$G$2,$C10<=$F$2),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I10</th><td style="text-align:left">=IF(AND($B10<>"Gold",$C10>=$G$3,$C10<=$F$3),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J10</th><td style="text-align:left">=IF(AND($B10<>"Gold",$C10>=$G$4,$C10<=$F$4),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K10</th><td style="text-align:left">=IF(AND($B10<>"Gold",$C10>=$G$5,$C10<=$F$5),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G11</th><td style="text-align:left">=IF(F11<>0,MAX(F$2:F10),F11)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H11</th><td style="text-align:left">=IF(AND($B11<>"Gold",$C11>=$G$2,$C11<=$F$2),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I11</th><td style="text-align:left">=IF(AND($B11<>"Gold",$C11>=$G$3,$C11<=$F$3),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J11</th><td style="text-align:left">=IF(AND($B11<>"Gold",$C11>=$G$4,$C11<=$F$4),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K11</th><td style="text-align:left">=IF(AND($B11<>"Gold",$C11>=$G$5,$C11<=$F$5),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G12</th><td style="text-align:left">=IF(F12<>0,MAX(F$2:F11),F12)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H12</th><td style="text-align:left">=IF(AND($B12<>"Gold",$C12>=$G$2,$C12<=$F$2),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I12</th><td style="text-align:left">=IF(AND($B12<>"Gold",$C12>=$G$3,$C12<=$F$3),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J12</th><td style="text-align:left">=IF(AND($B12<>"Gold",$C12>=$G$4,$C12<=$F$4),"T","F")</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K12</th><td style="text-align:left">=IF(AND($B12<>"Gold",$C12>=$G$5,$C12<=$F$5),"T","F")</td></tr></tbody></table></td></tr></table>

Note that I iterated that formula across ~850 columns with $s added/removed strategically and the paste special > Transpose feature. I then did a COUNTIF by column for "T", transposed that result vertically, and voila!

Surely there's an easier way; I'm ready to learn. Thanks in advance for any assistance provided!

[Win 7 / Excel 2013]

James006 · Jul 10, 2015

Hello,

You could test the following :

1. Delete contents in cell E1
2. Input in cell E2, the formula : =IF(B2="Gold",0,E1+1)
3. Copy the formula down

HTH

brystmar · Jul 10, 2015

Hi James,
Thanks for the reply. Unfortunately, that formula only works if there were no concurrent sessions. Note that there were many sessions with overlapping start/finish times. Thus, the suggested formula only allows for counting either starts since the last start or finishes since the last finish. I'm looking for starts since the last finish.

James006 · Jul 10, 2015

Hi,

Sorry I had not noticed there were overlapping start/finish times ...

Just for clarification purposes :

1. Are you allowed to sort your database or not ...?

2. Would you mind having a customized UDF ...?

Mackers · Jul 10, 2015

brystmar said:
Hi James,
Thanks for the reply. Unfortunately, that formula only works if there were no concurrent sessions. Note that there were many sessions with overlapping start/finish times. Thus, the suggested formula only allows for counting either starts since the last start or finishes since the last finish. I'm looking for starts since the last finish.

Hi brystmar

I pasted your data into A1:F12, with the numbers in A:A, the Tcode in B:B, the Status in C:C etc.

In G2, then dragged down, execute as an array function (Control+Shift+Enter): =MAX(IF($C$2:$C$12="Gold", IF($E$2:$E$12<=D2, $A$2:$A$12)))
In H2, then dragged down: =IF(C2="Gold", COUNTIFS($D$2:$D$12, ">"&INDEX($E$2:$E$12, MATCH(G2, $A$2:$A$12, 0)), $D$2:$D$12, "<="&D2), "")

Alternatively you could combine it into the following array function: =IF(C2="Gold", COUNTIFS($D$2:$D$12, ">"&INDEX($E$2:$E$12, MATCH(G2, MAX(IF($C$2:$C$12="Gold", IF($E$2:$E$12<=D2, $A$2:$A$12))) $A$2:$A$12, 0)), $D$2:$D$12, "<="&D2), "")

Hope that is what you're after

Mackers

brystmar · Jul 13, 2015

James006 said:
Hi,

Sorry I had not noticed there were overlapping start/finish times ...

Just for clarification purposes :

1. Are you allowed to sort your database or not ...?

2. Would you mind having a customized UDF ...?

Can sort these data anyway I wish, provided the transaction details for each row remain together. And I'm fully open to a UDF if that's the best solution.

brystmar · Jul 13, 2015

Mackers said:
Hi brystmar

I pasted your data into A1:F12, with the numbers in A:A, the Tcode in B:B, the Status in C:C etc.

In G2, then dragged down, execute as an array function (Control+Shift+Enter): =MAX(IF($C$2:$C$12="Gold", IF($E$2:$E$12<=D2, $A$2:$A$12)))
In H2, then dragged down: =IF(C2="Gold", COUNTIFS($D$2:$D$12, ">"&INDEX($E$2:$E$12, MATCH(G2, $A$2:$A$12, 0)), $D$2:$D$12, "<="&D2), "")

Alternatively you could combine it into the following array function: =IF(C2="Gold", COUNTIFS($D$2:$D$12, ">"&INDEX($E$2:$E$12, MATCH(G2, MAX(IF($C$2:$C$12="Gold", IF($E$2:$E$12<=D2, $A$2:$A$12))) $A$2:$A$12, 0)), $D$2:$D$12, "<="&D2), "")

Hope that is what you're after

Mackers

Hi Mackers,
Thanks for the response. I was unaware the COUNTIF function allowed operators like < or > in the criteria field. Tried this myself in the past without realizing the operator had to be formatted as a text string -- cool!

To clarify: when you pasted my data into A1:F12, you included a new leading column (which was previously just the row number) correct?

Unfortunately, I can't seem to get either solution you provided to work, even when confined to the 11 rows pasted above. I was careful to apply these formulas as an array (first selecting G2:G12, then paste + CSE; repeat for H2:H12) but still didn't field any data. Here's the result:

Excel 2012

A

B

C

D

E

F

G

H

1

TCode

Status

Start Timestamp

Finish Timestamp

# starts since the last Gold finish

G Formula

H Formula

2

N1HRRJ38BGMR79

Platinum

6/26/2015 15:21

6/26/2015 15:22

?

0

3

N1HRRLZN6P8L75

Silver

6/26/2015 15:25

6/26/2015 15:26

?

0

4

N1HRRN25DTHBTH

Silver

6/26/2015 15:27

?

0

5

N1HRRLYQGHSBZ3

Gold

6/26/2015 15:25

6/26/2015 15:31

?

0

6

N1HRRT82BK8959

Silver

6/26/2015 15:34

6/26/2015 15:35

?

0

7

N1HRRSTRHRTRBF

Silver

6/26/2015 15:33

6/26/2015 15:36

?

0

8

N1HRRQY9VK3XZF

Gold

6/26/2015 15:29

6/26/2015 15:39

?

0

9

N1HRRY97577LVK

Silver

6/26/2015 15:40

6/26/2015 15:41

?

0

10

N1HRRRW5X57YK8

Bronze

6/26/2015 15:31

6/26/2015 15:54

?

0

11

N1HRS8D9GG28MB

Silver

6/26/2015 15:57

?

0

12

N1HRSXPXHGZXNN

Gold

6/26/2015 16:13

6/26/2015 16:21

?

0

Sheet2

<table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" >Array Formulas<table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G2:G12</th><td style="text-align:left">{=MAX(IF($C$2:$C$12="Gold", IF($E$2:$E$12<=D2, $A$2:$A$12)))}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H2:H12</th><td style="text-align:left">{=IF(C2="Gold", COUNTIFS($D$2:$D$12, ">"&INDEX($E$2:$E$12, MATCH(G2, $A$2:$A$12, 0)), $D$2:$D$12, "<="&D2), "")}</td></tr></tbody></table>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.</td></tr></table>

Additionally, I believe the one-step formula needs the following string removed to work as you intended:

Original:
=IF(C2="Gold", COUNTIFS($D$2:$D$12, ">"&INDEX($E$2:$E$12, MATCH(G2, MAX(IF($C$2:$C$12="Gold", IF($E$2:$E$12<=D2, $A$2:$A$12)))$A$2:$A$12, 0)), $D$2:$D$12, "<="&D2), "")

Corrected?
=IF(C2="Gold", COUNTIFS($D$2:$D$12, ">"&INDEX($E$2:$E$12, MATCH(G2, MAX(IF($C$2:$C$12="Gold", IF($E$2:$E$12<=D2, $A$2:$A$12))), 0)), $D$2:$D$12, "<="&D2), "")

And is this formula recursive? My attempts using this formula were in cells G2:G12, and I noticed the MATCH lookup value is calling this same column. Also tried using it in H2:H12 paired with the first formula you suggested above in G2:G12. My attempts with this formula all end up failing the initial IF statement. Where am I going wrong?

Thanks

James006 · Jul 13, 2015

Hi,

For the sake of clarity ... would you mind indicating the expected results for your column F ...

Mackers · Jul 13, 2015

Hi brystmar

I can't check my workings for the combined formula at the moment, but it should just be the bottom formula with G2 replaced by the MAX() formula.

Regarding how to input the formulas: only the top formula is an array formula, the other one is a standard input. You should input it only into the cell G2 then execute with CSE. After it is executed in the single cell you should drag it down. If you instead highlight all of the cells and execute the formula, Excel will expand the array for the single formula in G2 out into G2:G12 - we don't want this.

To explain how I intended it to work: The first formula, let's call it formula A, first checks if each row is a "Gold" row. If it is, the formula then checks if the corresponding end date in E2:E12 is prior to the start date of the current row, D2. If it is, it returns the matching row numbers A2:A12. If it isn't, we return FALSE and discount that row (i.e. it was either not "Gold" or ended after our current start date). We then take the MAX() of this to find the "Gold" row that started most recently and has also finished. So Formula A has returned us the Row, found in A2:A12, of the most recently finished "Gold" show. From this we can find all the shows that have started since then, but did not start after our current "Gold" show.

The second formula, formula B, then has to count how many rows are "Gold", have a start date D2:D12 after (greater than) the end date of the most recently finishing show found in formula A, and have a start date D2:D12 prior to our current start date, D2. This is essentially "gold shows that have started since the last one ended, but haven't ended". Unfortunately I'm not on a machine with Excel so I can't check if I messed up the formula B, but hopefully this makes sense and you can tweak the inputs if I've had a brain fart (very possible).

Hope that helps

Mackers

Counting challenge with complex criteria

brystmar

New Member

Excel Facts

James006

Well-known Member

brystmar

New Member

James006

Well-known Member

Mackers

Well-known Member

brystmar

New Member

brystmar

New Member

James006

Well-known Member

Mackers

Well-known Member

Similar threads

Forum statistics

Share this page

Counting challenge with complex criteria

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

New Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock