CODEWITHSUNDEEP
excelvbamultiple-columns
Maya Cat
Maya Cat

Reputation: 29

Excel - Counting value occurrence in two separate columns

| Start Time | End Time |  
| 2300       | 100      |  
| 400        | 2300     |

etc..

So I have two columns w/ thousands of rows, of time data (which is stored as whole numbers not as times) and I need to count how many rows either start OR end between 2000 and 800 (8pm and 8am). If it was one column, or only rows that start AND end between that period I could do it but I'm at a loss currently. Any help would be very much appreciated.

Upvotes: 0

Views: 87

Answers (2)

Scott Craner
Scott Craner

Reputation: 152450

Use SUMPRODUCT:

=SUMPRODUCT(((A2:A4>2000)+(A2:A4<800)+(B2:B4>2000)+(B2:B4<800)>0)*1)

enter image description here

Upvotes: 1

BruceWayne
BruceWayne

Reputation: 23283

Does this formula work:

=IF(AND(A1>=2000,B1<=800),"1","")

Upvotes: 0

Related Questions