Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: SUMIF Using Greater/Less Than Symbols In Text Criteria

1. I agreed to these rules
Join Date
20th October 2008
Posts
2

## SUMIF Using Greater/Less Than Symbols In Text Criteria

Hi,

I'm trying to get a simple Sumif formula to work. I have used this formula a hundred times before but this time the results are inaccurate. I suspect that it may be because of the math symbols which are used in the criteria text i.e. < and >

My formula is as follows:

=SUMIF('Raw Data Export'!D:D,"< No Project >",'Raw Data Export'!G:G)

A data extract from the sheet named "Raw Data Export" is attached.

Regards,

Leanne
Last edited by Dave Hawley; October 21st, 2008 at 08:48.

Excel Video Tutorials / Excel Dashboards Reports

2. Senior Member
Join Date
18th February 2004
Location
Florida
Posts
212

## Re: Sumif A Cell Matches Text When The Text Contains Math Symbols

Hello,

Not sure why the math symbols are not recognized. How about as a work around, use the asterisk as a wildcard.

i.e
SUMIF( 'Raw Data Export'!D:D,"* No Project *",'Raw Data Export'!G:G)

HTH

JL

Excel Video Tutorials / Excel Dashboards Reports

3. I agreed to these rules
Join Date
20th October 2008
Posts
2

## Re: Sumif A Cell Matches Text When The Text Contains Math Symbols

The workaround works for me. Thanks, I didn't know wild cards could be used. It is weird why the original formula wouldn't work though.

Excel Video Tutorials / Excel Dashboards Reports

4. ## Re: Sumif A Cell Matches Text When The Text Contains Math Symbols

Not sure why the math symbols are not recognized
I reckon the < is interpreted as less than, as in =COUNTIF(someRange, "<0"). You'd think the tilde (~) would escape it, but it doesn't.

5. ## Re: Sumif A Cell Matches Text When The Text Contains Math Symbols

Thought maybe this would work (found from an answer by SHG elsewhere), but it doesn't:
=SUMIF(D:D,CHAR(139) & " No Project " & CHAR(155),G:G)

SUMPRODUCT will work though:
=SUMPRODUCT(--(D2:D50="< No Project >"),G2:G50)

Excel Video Tutorials / Excel Dashboards Reports

6. ## Re: Sumif A Cell Matches Text When The Text Contains Math Symbols

Aha! =SUMIF(D:D, "=< No Project >", G:G )

7. ## Re: SUMIF Using Greater/Less Than Symbols In Text Criteria

Personally, I would Replace all < * > with ( * ) to save further problems you may not even know about.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno