Sumproduct with OR

[Guest]

Hi,

I searched through some of the questions here to find a solution to this,
however, i'm more confused!!

I would like to return a total where one of the columns i'm totalling has
more than one criteria. Specifically in this formula i would like to total
values for the criteria below, however for Col. I this should only be where
the string is either "MI" or "ILS"
The formula below returns #Value.

=SUMPRODUCT(--(DataTable!$E$5:$E$65536=INDEX(Tbl_Mths,MATCH($U$4,Tbl_Dropdown,0),2)),--(DataTable!$F$5:$F$65536="Base"),--(DataTable!$H$5:$H$65536="SB
SPU"),--OR(DataTable!$I$5:$I$65536="MI"),(DataTable!$I$5:$I$65536="ILS"),(DataTable!$U$5:$U$65536))

I had written this formula where the total for MI was returned and added
this to the ILS but i'd like to thinker what i'm trying to do is slicker!
Any help on this would be great.

Thanks
John

 

[Guest]

try:

=SUMPRODUCT((DataTable!$E$5:$E$65536=INDEX(Tbl_Mths,MATCH($U$4,Tbl_Dropdown,0),2))*(DataTable!$F$5:$F$65536="Base")*(DataTable!$H$5:$H$65536="SB
SPU")*(DataTable!$I$5:$I$65536={"MI","ILS"})*(DataTable!$U$5:$U$65536))

 

[Guest]

Hi Toppers,

Thanks for the answer. I'd like to probe
(DataTable!$I$5:$I$65536={"MI","ILS"})
to include LAH 1 through to 100 but dont want to write into the array.
Rather than type "LAH 1", "LAH2" etc etc i tried LAH* where * is 1 to 100 but
this returned an error. How would i include this please?

Thanks
John

 

[Guest]

Try:

=SUMPRODUCT(--(ISNUMBER(FIND({"LAH","MI","ISL"},I5:I65536))*(H5:H65536)))

 

[Peo Sjoblom]

Here's a version using the unary minuses as in the OP's original formula

=SUMPRODUCT(--(DataTable!$E$5:$E$65536=INDEX(Tbl_Mths,MATCH($U$4,Tbl_Dropdown,0),2)),--(DataTable!$F$5:$F$65536="Base"),--(DataTable!$H$5:$H$65536="SB
SPU"),--((DataTable!$I$5:$I$65536="MI")+(DataTable!$I$5:$I$65536="ILS")>0),DataTable!$U$5:$U$65536)

 

[Sandy Mann]

Peo,

In this instance do you actually require the double unery or the >0 in

--((DataTable!$I$5:$I$65536="MI")+(DataTable!$I$5:$I$65536="ILS")>0)

surely as any cell can not hold exclusively "MI" or "ILS" at the same time
then

((DataTable!$I$5:$I$65536="MI")+(DataTable!$I$5:$I$65536="ILS"))

would return 1 or 0


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Dyslexic fingers again! I meant of course double unary

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Peo Sjoblom]

Could have been worse, at least you didn't type urinary <bg>

You are of course right, it's not needed since they are in the same column,
it's just me wanting to cover all unforeseen angles and different seasons