On Sat, 31 Dec 2005 13:38:48 -0600, via135
wrote:


sorry mr ron!

i am simply getting a zero while using ur formula!
r u referring to give the fruit name & month name as "cell reference"
or simply the "text value" such as "orange", "apple"..and "jan",
"feb"...?

pl clarify!


The formula should work using either cell references or text strings.

However, the month names are assumed to be text strings, as you wrote in your
initial post, and NOT Excel dates..

For example, given the following table in A1:G7

Jan Feb Mar Apr May Jun
apple 40 33 31 35 26 27
banana 29 29 34 18 33 40
mango 36 17 22 34 35 31
lemon 19 24 19 28 35 28
orange 16 18 21 37 35 36
grapes 28 18 17 30 22 20


The formula:

=SUMPRODUCT(OFFSET(INDEX($A$1:$G$7,MATCH(
"mango",$A$1:$A$7,0),MATCH("Feb",$A$1:$G$1,0)),,,, 3))

will give a result of 73 == the number of mangos sold for three (3) months
starting in February -- i.e. Feb=17; Mar=22; Apr=34


--ron