SUMPRODUCT(-- Query for Tom Oglivy or others......

[Guest]

I'm using the wonderful formula:

=SUMPRODUCT(--(WTCode="Web
Mail"),--(WTMonthRange=C$8),--(WTYearRange=C$7),WTUnits)

to find a sum based on all the rows with "Web Mail" in the first column.
Any idea how to add a wildcard to this syntax so it would pick up any cell in
that column starting with "Web"? (i.e. Web* - so it would also add "Web
Email", "Web User", "Web Enquiry", etc....)

I've been repeating the full formula above for each occurence, but my
formulae are reaching the max length....

THANK YOU, as always!!!!

Marika

 

[Bob Phillips]

=SUMPRODUCT(--(LEFT(WTCode,3)="Web"),--(WTMonthRange=C$8),--(WTYearRange=C$7
),WTUnits)

or

=SUMPRODUCT(--(ISNUMBER(FIND(WTCode="Web"))),--(WTMonthRange=C$8),--(WTYearR
ange=C$7),WTUnits)

to find Web anywhere in the cell

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Arvi Laanemets]

Hi

=SUMPRODUCT(--(LEFT(WTCode,3)="Web"),--(WTMonthRange=C$8),--(WTYearRange=C$7
),WTUnits)

Arvi Laanemets

 

[Dave O]

Nice one, Bob! It be an idea to use SEARCH, rather than FIND, since
Search is not case sensitive.

 

[Bob Phillips]

Dave,

I think that is a design decision, and not one we should presume to make, as
to whether the test should or should not be case sensitive.

I would have been more helpful though to point out the possibilities though.

Regards

Bob