....also - Roger is right - Sorry - I pasted the wrong formula! I do count
with that one and sum with another. I blame late afternoon and formula
frustration syndrome! :)


"Mark11" wrote:

Thanks again Roger & Stefi.
Roger's solution worked!
Stefi's didn't seem to - not sure why. Maybe because "Diesel" was being
found in a phrase. I agree I wasn't clear, sorry.
Cheers.

"Stefi" wrote:

Your request is not very clear, but if B8 contains "DIESEL" then maybe you
need this formula:
=SUMPRODUCT(--(INDIRECT(B3&B6):INDIRECT(B3&B7)=B8),--(INDIRECT(B5&B6):INDIRECT(B5&B7)<F4),INDIRECT(B5&B 6):INDIRECT(B5&B7))

Regards,
Stefi

€žMark11€� ezt Ã*rta:

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.