Count Samples by Type and Time
I need to count how many 'Blood Samples' were collected before 10:00 AM.
=COUNTIFS(B2:B11, "Blood Sample", C2:C11, "<10:00 AM") How it works: The `COUNTIFS` function is perfect for counting rows that meet multiple conditions. In this scenario, it first checks each row in the 'Sample Type' column (B2:B11) to see if it matches 'Blood Sample'. Simultaneously, for those rows that match, it then checks the corresponding 'Collection Time' (C2:C11) to see if it is earlier than 10:00 AM. Only rows that satisfy *both* conditions are included in the final count, providing an accurate tally of specific samples collected within a desired timeframe.
Data Setup
| Sample ID | Sample Type | Collection Time | Patient ID |
|---|---|---|---|
| S001 | Blood Sample | 08:30 AM | P101 |
| S002 | Urine Sample | 09:15 AM | P102 |
| S003 | Blood Sample | 11:00 AM | P103 |
| S004 | Blood Sample | 09:45 AM | P104 |
| S005 | Swab Sample | 10:30 AM | P105 |
| S006 | Blood Sample | 07:50 AM | P106 |
| S007 | Blood Sample | 10:15 AM | P107 |
| S008 | Urine Sample | 08:00 AM | P108 |
| S009 | Blood Sample | 09:00 AM | P109 |
| S010 | Blood Sample | 12:00 PM | P110 |
Step-by-Step Guide
**Step 1: Identify the 'Sample Type' range.** In your Excel sheet, this is the column containing the type of sample (e.g., 'Blood Sample', 'Urine Sample'). For our mock data, this is `B2:B11`.
**Step 2: Specify the first criterion.** We are looking for 'Blood Sample', so the criterion is `"Blood Sample"`.
**Step 3: Identify the 'Collection Time' range.** This is the column containing the time each sample was collected. For our mock data, this is `C2:C11`.
**Step 4: Specify the second criterion.** We need samples collected 'before 10:00 AM'. In Excel, this is expressed as `"<10:00 AM"`.
**Step 5: Combine into the COUNTIFS function.** Enter the complete formula into an empty cell: `=COUNTIFS(B2:B11, "Blood Sample", C2:C11, "<10:00 AM")`.