Sumproduct Multiple Data

[jay_b]

Hi All

I have a Sumproduct Formula as below

=SUMPRODUCT(--(Data!$E$2:$E$65536=Sheet1!$A2),--(Data!$H$2:$H$65536="Cancellation, Property Damage & Liability") --(Data!$F$2:$F$65536=B$1),1*ISTEXT(Data!$A$2:$A$65536))

Where i am looking at "Cancellation, Property Damage & Liability" i want to be able to add other text fields to this section from the same data range to include multiple tect field selections

Is this possible?

Many thanks

jay_b

[teylyn]

Hi,

you can incorporate an OR() style check into sumproduct thus:

=SUMPRODUCT(--(Data!$E$2:$E$65536=Sheet1!$A2),--((Data!$H$2:$H$65536="Cancellation, Property Damage & Liability")+(Data!$H$2:$H$65536="Other text")+(Data!$H$2:$H$65536="still other text")), --(Data!$F$2:$F$65536=B$1),1*ISTEXT(Data!$A$2:$A$65536))


this will generate three arrays of TRUE and FALSE which are added, so TRUE+FALSE+FALSE = TRUE, which means, the value will be included.


hth

[daddylonglegs]

Another way would be to list all the text strings that you need to check against somewhere on the worksheet, e.g. C1:C10, then change this part

--(Data!$H$2:$H$65536="Cancellation, Property Damage & Liability")

to this

--ISNUMBER(MATCH(Data!$H$2:$H$65536,C1:C10,0))

Note: unless you really have 65000 rows of data it would be better to restrict the range to a smaller size

[jay_b]

Many thanks Teylyn and daddylonglegs

Much appreciated