SUMPRODUCT with two wildcards.

[Hasntgotaclue]

Hi guys, i'm having a little trouble with this.

I have three columns A, D and I in this formula.

A is a text column containing week and a month and values, which could be as follows:

Week 3 May
Week 4 May
Week 1 August
May
Week 1 September

D is a numberic column contaning an integer value.

I is another text column containing values such as:

Sent
Rejected
Rejected, but now Sent

I've been trying to use SUMPRODUCT to sum the values of D where A contains "May" and I contains "Sent". I know SUMPRODUCT doesnt allow wildcards, so i've been trying to use ISNUMBER(SEARCH which i have done successfully for the "May" part. However i cant get it to work with both "May" and "Sent".

I know the types of data is a mess, and i'd fix it if it wasnt such a huge sheet used by lots of different users who all use slightly different entries. The single words are all i can work with really.

Looking forward to hearing from you.

[Carim]

Hi,

Why don't you post your formula ...?

[Hasntgotaclue]

Because Excel crashed just before i posted and i lost it all Was trying not to save, because as i said its a large shared workbook and it'll upset people if i start adding in half finished formulas.

I know, i should've saved it elswhere... stupid me

[arthurbr]

This site will help :
http://www.xldynamic.com/source/xld....T.html#classic
Look for example 9

[Hasntgotaclue]

Think it was something like this:

=SUMPRODUCT(ISNUMBER(SEARCH("may",A2:A13856))*(ISNUMBER(SEARCH("Sent",I1:I13856)))*D2:D13856)

[Hasntgotaclue]

Arthur, thanks for that, i've got it now.

=SUMPRODUCT(--(ISNUMBER(SEARCH("May",A3:A13857))),--(ISNUMBER(SEARCH("Sent",I3:I13857))),--(D3:D13857))

[Hasntgotaclue]

Why did i need the double negative operators though?

[arthurbr]

Have a look a little higher on the same page

Cheers