limited countif

**katja328** · 10-22-2009, 04:24 PM

I am trying to get excel to count the following

Count all fields in column B that have zeros in them but only until the number turns positiv.

For example

B1: 0
B2: 0
B3: 1000
B4: 1000
B5: 1000
B6: 1000
B7: 0
B8: 0

Ideally my outcome is: 2 and not 4

**rwgrietveld** · 10-22-2009, 04:47 PM

Just make the range smaller. Look for the first value that is > 0 with

=MATCH(1,B3:B15,1)

Then use indirect to interpret the string

"$B$3:B"&(2+MATCH(1,B3:B15,1))

B17 becomes:

=COUNTIF(INDIRECT("$B$3:B"&(2+MATCH(1,B3:B15,1))),"0")

**katja328** · 10-22-2009, 05:11 PM

What does the 2 stand for?

This works fine except for when the amounts of free rent are greater than 2. It seems to count one less than is actually there.

**rwgrietveld** · 10-23-2009, 02:23 AM

Yoyr range starts from row 3 instead of 1. Therefore I need an offset of 2.

Can you give an example of the second comment?

**DonkeyOte** · 10-23-2009, 02:29 AM

I'm with rwg for approach but would probably opt for non-volatile INDEX over INDIRECT, eg (based on your earlier upload):

C1: =IF(B3,"",COUNTIF(B3:INDEX(B3:B15,MATCH(1,B3:B15)),0))

**rwgrietveld** · 10-23-2009, 02:35 AM

Never seen such use of INDEX. I like it.
Good addition DonkeyOte

**DonkeyOte** · 10-23-2009, 02:40 AM

In most cases INDEX can be used in preference to volatiles like OFFSET and INDIRECT... the latter not so when the sheet reference is a dynamic variable. OFFSET is sometimes easier from a syntax perspective but wherever possible I adopt INDEX.
(should add that INDEX gives impression of being Volatile but in reality is not)

In short INDEX is incredibly powerful (& flexible) but is also slow if used on large ranges... eg

=SUM(INDEX((A1:A10000="a")*(B1:B10000="b")*C1:C10000,0))

is likely to prove slower than a SUMPRODUCT / CSE

=SUMPRODUCT((A1:A10000="a")*(B1:B10000="b")*C1:C10000)
=SUM(IF((A1:A10000="a")*(B1:B10000="b"),C1:C10000))) - CSE

**katja328** · 10-23-2009, 01:14 PM

I went ahead and used this formula on my actual workbook (very different from the sample uploaded). I substituted the 2 for a 3 I am starting in row 4.

=COUNTIF(INDIRECT("$O$4:O"&(3+MATCH(1,O4:O15,1))),"0")

This works well for my first calculations on how many months nothing is paid.

Everything works perfectly until the term ends and the amount reverts back to zero.

Currently, I am using a simple countif function to count months with the corresponding rate. After the rent term has started, I would like for excel to count the months only if a dollar amount is listed.

Basically, count the number of months that the amount in O4:O123 is the same as in J21 but do not count if that amount is "0"

Sorry about this confusion.