limited countif

**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.

limited countif

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Re: limited countif

Re: limited countif

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions