Hi Mark

Wrong number of parentheses in your formula, it should be
=SUMPRODUCT(--(ISNUMBER(FIND($B$8,(INDIRECT($B$3&$B$6)
:INDIRECT($B$3&$B$7))))),--(INDIRECT($B$5&$B$6):INDIRECT($B$5&$B$7)<$F4))

But that will only give you the Count of the number of Items where Diesel is
less than 1000.
If you want the SUM, you need to add the second range AH2:AH207 again.

=SUMPRODUCT(--(ISNUMBER(FIND($B$8,(INDIRECT($B$3&$B$6)
:INDIRECT($B$3&$B$7))))),--(INDIRECT($B$5&$B$6):INDIRECT($B$5&$B$7)<$F4),
(INDIRECT($B$5&$B$6):INDIRECT($B$5&$B$7)))
--
Regards
Roger Govier

"Mark11" wrote in message
...
Actually, it worked for simple sumproduct formulae, but once I tried
=SUMPRODUCT(--(ISNUMBER(FIND($B$8,(indirect($B$3&$B$6):indirect( $B$3&$B$7)))),--(indirect($B$5&$B$6):indirect($B$5&$B$7)<$f4)))

Where B8 is text, B3=AB, B5=AH, B6=2, B7=207 and f4=1000
It was supposed to look to AB2:AB207 and find, say, "DIESEL" (f4) and sum
all totals of diesel orders in (AH2:AH207) under 1000.
It just keeps giving #VALUE and says "All arrays must have the same
dimensions"
Clearly I don't know what this means, nor how to fix it.
Any ideas?

"Mark11" wrote:

Thanks Roger and Stefi. Both those solutions worked perfectly.
Cheers.

"Stefi" wrote:

If I were you I'd structure entered data like this:
A1: condition column - D
B1: sum column - E
A2: row from - 3
B2: row to - 50

In this case the formula:

=SUMPRODUCT(--(INDIRECT(A1&A2):INDIRECT(A1&B2)<1000),(INDIRECT(B 1&A2):INDIRECT(B1&B2)))

Regards,
Stefi

=SUMPRODUCT(--(INDIRECT(A1&A2):INDIRECT(A1&B2)<1000),(INDIRECT(B 1&A2):INDIRECT(B1&B2)))

€žStefi€� ezt Ã*rta:

=SUMPRODUCT(--(INDIRECT(A1):INDIRECT(A2)<1000),(INDIRECT(B1):IND IRECT(B2)))

Be careful! Ranges defined in A1,A2 and B1,B2 must be of same length,
e.g.
if you enter A2=50 and B2=51 then you get error!

Regards,
Stefi

€žMark11€� ezt Ã*rta:

I am trying to make formulae that is used regularly be more user
friendly.
Is there a way I can enter a range and use that input data in a
formula.
e.g. in A1 type "D3" and A2 type "D50" B1 type "E3" B2 type "E50"
then use
D3:D50 and E3:E50 in a formula, e.g.
=SUMPRODUCT(--(D3:D50<1000),--(E3:E50)).
I have tried many variations but none seem to work.
Any help would be appreciated.