Possible to shorten a sumproduct formula?

[freseh]

Hi

I need help to shorten the formula below, if it's possible. I thin
that the criterias are mutually exclusive, but I can't get it work whe
I try to put the criterias together.

Formula as follow:
=SUMPRODUCT(--(ISNUMBER(FIND(Site&data!M35;Volym!B5:B2000))+ISNUMBER(FIND(Site&data!M36;Volym!B5:B2000))+ISNUMBER(FIND(Site&data!M37;Volym!B5:B2000))+ISNUMBER(FIND(Site&data!M38;Volym!B5:B2000))+ISNUMBER(FIND(Site&data!M39;Volym!B5:B2000))+ISNUMBER(FIND(Site&data!M40;Volym!B5:B2000))+ISNUMBER(FIND(Site&data!M41;Volym!B5:B2000))+ISNUMBER(FIND(Site&data!M42;Volym!B5:B2000))+ISNUMBER(FIND(Site&data!M43;Volym!B5:B2000))+ISNUMBER(FIND(Site&data!M44;Volym!B5:B2000))+ISNUMBER(FIND(Site&data!M45;Volym!B5:B2000))+ISNUMBER(FIND(Site&data!M46;Volym!B5:B2000)));Volym!C5:C2000)

Thanks in advance
Best Regards
Fre

 

[Harlan Grove]

I need help to shorten the formula below, if it's possible. I think
that the criterias are mutually exclusive, but I can't get it work when
I try to put the criterias together.

Formula as follow:

[reformatted]

=SUMPRODUCT(
--(ISNUMBER(FIND(Site&data!M35;Volym!B5:B2000))
+ISNUMBER(FIND(Site&data!M36;Volym!B5:B2000))
+ISNUMBER(FIND(Site&data!M37;Volym!B5:B2000))
+ISNUMBER(FIND(Site&data!M38;Volym!B5:B2000))
+ISNUMBER(FIND(Site&data!M39;Volym!B5:B2000))
+ISNUMBER(FIND(Site&data!M40;Volym!B5:B2000))
+ISNUMBER(FIND(Site&data!M41;Volym!B5:B2000))
+ISNUMBER(FIND(Site&data!M42;Volym!B5:B2000))
+ISNUMBER(FIND(Site&data!M43;Volym!B5:B2000))
+ISNUMBER(FIND(Site&data!M44;Volym!B5:B2000))
+ISNUMBER(FIND(Site&data!M45;Volym!B5:B2000))
+ISNUMBER(FIND(Site&data!M46;Volym!B5:B2000))
);
Volym!C5:C2000
)


So you're checking whether values in Site&data!M35:M46 appear anywhere in
Volym!B5:B2000. To check whether the values in Site&data!M35:M46 represent
mutually exclusive criteria you could enter the following array formula.

=MAX(MMULT(--ISNUMBER(FIND(TRANSPOSE(Site&data!M35:M46);Volym!B5:B2000));
ROW(Site&data!M35:M46)^0))

If the result is 0, you have nothing more to do at all - there are no matches
whatsoever. If the result is 1, the values in Site&data!M35:M46 represent
mutually exclusive criteria for the *current* values in Volym!B5:B2000. If the
result is greater than 1, Site&data!M35:M46 don't represent mutually exclusive
criteria. However, even if the criteria aren't mutually exclusive, presumably
you'd want to include the corresponding value in Volym!C5:C2000 in the sum. That
so, the safest and shortest formula would be the array formula

=SUM((MMULT(--ISNUMBER(FIND(TRANSPOSE(Site&data!M35:M46);Volym!B5:B2000));
ROW(Site&data!M35:M46)^0)>0)*Volym!C5:C2000)

This assumes Volym!C5:C2000 contains nothing but numbers and blank cells. If it
could contain text, then you'd want to use the *array* formula

=SUMPRODUCT(--(MMULT(--ISNUMBER(FIND(TRANSPOSE(Site&data!M35:M46);
Volym!B5:B2000));ROW(Site&data!M35:M46)^0)>0);Volym!C5:C2000)

If you're ABSOLUTELY CERTAIN the values in Site&data!M35:M46 represent mutually
exclusive criteria, then you *could* use the nonarray formula

=SUMPRODUCT(SUMIF(Volym!B5:B2000;"*"&Site&data!M35:M46&"*";Volym!C5:C2000))