Search code examples
excelworksheet-function

Counting between 2 words

I have something similar to the following table:

| Jan|
| 5 |
| 10 |
| 20 |
| 50 |
| Mar|
| 1 |
| 101|
| 6 |
I need come function in which will count the value between Jan and Mar. Result:4

Thanks in advance.

Solution

  • Depending on what you need, I can see a few possibilities.

    1) If you know exactly where "Jan" (say, A1) and "Mar" (say, A6) will be, you can just use =COUNT(A2:A5). This is useful when you know where your months will be.

    2) If you don't know exactly where "Jan" and "Mar" will be, you need to use a variation of Match which might look like =MATCH("Jan", A:A, 0) in B1 and then =MATCH("Mar", A:A, 0) in C1. This will give you the row number of "Jan" and "Mar", which you can then plug back in to =COUNT().

    A sample formula to count would look like this:

    =COUNT(INDIRECT("A"&B1):INDIRECT("A"&C1))-2

    The -2 is to account for the fact that you're including those rows.