Replies

But it does…

=IF(C7*D7>0,D7/C7,””)

The >0 bit is there, because you want to exclude negative numbers.

Aladin

How about

=IF(COUNTBLANK(A1:A2),”N/A”,IF(A1,(A2-A1)/A1,”N/A”))

where you can replace “N/A” by just #N/A without quotes if so desired?

Aladin

In order to get the last occurrence of a given month, use one of:

=INDEX(A2:A6,MATCH(E1,TEXT(A2:A6,”mmmm”))) where E1 is e.g., “June”,

=INDEX(A2:A6,MATCH(E1,MONTH(A2:A6))) where E1 is e.g., 6.

Both are again array-entered (using control+shift+enter).

I must leave the VBA part of your question to someone else.

Aladin

Let A1:C5 in Sheet1 house the sample you provided.

Let A1:A7 in Sheet2 house the input data for which the associated Ref#’s must be retrieved from the data in Sheet1.

In B2 in Sheet2 enter and copy down:

=SUMPRODUCT((A2>=Sheet1!$B$2:$B$5)*(A2<=Sheet1!$C$2:$C$5)*(Sheet1!$A$2:$A$5))

Aladin

It’s caused by “Extend list formats and formulas” on the Edit tab if this feature is checked.

Aladin

Try:

=SUM(OFFSET($A$1,0,0,3,1))-SUM(OFFSET($A$4,0,0,3,1))

Aladin

Array-enter, using control+shift+enter:

=SUM(IF(LEN(A2:A8),1/COUNTIF(A2:A8,A2:A8)))

where A2:A8 houses the dates to count.

Aladin

=INDEX(A2:A6,MATCH(E1,TEXT(A2:A6,”mmmm”),0))

where E1 houses a month name like “June” as criterion,

or

=INDEX(A2:A6,MATCH(E1,MONTH(A2:A6),0))

where E1 houses a month number like 6 as criterion.

These formulas must be array-entered (using control+shift+enter).

Aladin

I prefer the MATCH formula myself.

However, the COUNTIF formula should work too:

=COUNTIF(A1:D1,”Overdue”)>0

I believe you omitted the >0 bit, that’s why you get a count instead of a logical value.

Regards,

Aladin

Counting and summing depending on a single condition should be done with COUNTIF and SUMIF, respectively. Both accept constants like 7, “B”, a cell housing a constant, or a computation that evaluates to a scalar value. Examples:

=COUNTIF(A1:A10,MAX(A1:A10)), where the MAX bit evaluates to a scalar;

=SUMIF(A1:A10,”>=”&AVERAGE(A1:A10),B1:B10), where the AVERAGE bit also evaluates to a scalar.

For counting and summing depending on multiple conditions, you need to devise either (a) SUMPRODUCT or array-formulas or ( formulas with database functions.

For (a), see also:

http://www.mrexcel.com/wwwboard/messages/8961.html%5B/url%5D

Aladin

=ISNUMBER(MATCH(“Overdue”,A1:D1,0))

or

=COUNTIF(A1:D1,”Overdue”)>0

Aladin

Bruce,

If you’re willing to download Longre’s Morefunc add-in and add it to your system,
the following array-formula will give you the desired counts:

=SUM(IF(ISNUMBER(SEARCH(D2,UNIQUEVALUES($A$2:$A$7&$B$2:$B$7))),1))

where A2:A7 holds the house numbers, B2:B7 the pet types, and D2 a condition/criterion like “dog”.

In case it’s needed: To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

Morefunc is downloadable from:

http://longre.free.fr/english/index.html

Aladin

In F2 enter:

=A2*B2-SUM(EVAL(“{“&SUBSTITUTE(C2,CHAR(10),”,”)&”}”))

In order to use EVAL you need to download the Longre’s Morefunc add-in from:

http://longre.free.fr/english/index.html

Aladin

Ricky,

Given your new specs,

in C3 enter:

=B10-MOD(B10-1,7)

The rest stays the same as in my previous post.

Aladin