I am trying to sum if 3 conditions are met - if I5:I279 is = to "hired" and if E5:E279 is not blank and if G5:G279 doesn't contain "lm" count it., giving me the total. in another field. =SUMPRODUCT(--(I$5:I$279="Hired"),--(E5:E279<>""),--(G5:G279<>"LM")) I tried this but doesn't appear to be excluding the ones that are blank in E5:E279.

What is creating the "Blanks" in E5:E279? Are they truly blank or do they contain a formula? If it's a formula, please post it. Using this table - with only text in the cells (i.e. no formulas) - your formula returns 1:

Table 1E F G H I 5 Word LM Hired 6 Word Hired 7 Word LM Not Hired 8 Hired 9 Word LM Not HiredUsing this table, your formula returns 2:

Table 2E F G H I 5 Word LM Hired 6 Word Hired 7 Word LM Not Hired 8 Word Hired 9 Word LM Not HiredAre those results correct?

hello,

no formula in cells. Table one shows my sheet except in some of the rows in G there are other names besided LM which is what I want to count. - so count all in G that is not = to LM where I iss = to Hired but only if E has something in it and is not blank.

You didn't answer my question. Given the tables I showed, regardless of what is in the columns, are the results correct? Let's try it this way:

Your formula returned 2 with this table. Is that correct?

Table 3E F G H I 5 Word 1 LM Hired 6 Word 2 Hired 7 Word 3 LM Not Hired 8 Word 4 Not LM Hired 9 Word 5 LM Not Hired

sorry yes it did and should have only returned 1

Now you are confusing me. I've given you 3 different tables and you said " yes it did and should have only returned 1".Which table(s) should only have returned 1?

I have edited my posts and numbered the 3 tables, 1 through 3. Please tell me which ones returned the correct results and which ones didn't. For any that didn't return the correct result, please tell me what you expect the result to be and why - based on the contents of the cells.

Table 3 - table 1 and 2 did not return the correct results.

Don't take this the wrong way, but do you read my posts in their entirety before you respond? Let me try this one more time:

For any that didn't return the correct result,

please tell me what you expect the result to be and why - based on the contents of the cells.Please keep in mind that I can not see your spreadsheet from where I'm sitting nor can I read your mind. If you don't tell

whythe results of the formula are wrong - based on the examples I used - I can't help you.Let's look at what you asked for and then look at Table 2.

count all in G that is not = to LM where I iss = to Hired but only if E has something in it

Table 2E F G H I 5 Word LM Hired 6 Word Hired 7 Word LM Not Hired 8 Word Hired 9 Word LM Not Hired

count all in G that is not = to LM

- G6 & G8 are not equal to LM

I iss = to Hired

- I6 & I8 are equal to Hired

only if E has something in it

- E6 & E8 both have something in the cell.I see that as 2 occurences that fit your 3 criteria.

Please tell me

whythe result should not be 2.

I hope my last reply didn't post --- system went crazy

So here is what my sheet looks like and the result I need to have.E F G H I

5 Word LM Hired

6 Hired

7 Word Not LM Hired

8 Hired

9 Word LM HiredThe result should be 1

count all in G that is not = to LM

I is = to hired but only if E has something in it

re: The result should be 1.And that is exactly what your formula returns.

What's the problem?

OMG - the field that I was comparing the LM to actually had more in the feld -- example LM - OD/VAO/WS/AP/HH/AM/GC and I was only using the 1st part of the field thus giving me an incorrect result.

Is there a wildcard charcter that could be used to identify the characters after LM?Thanks for putting up with me, I really appreciate it!

re: " Is there a wildcard charcter that could be used to identify the characters after LM?"What do you mean by "identify the characters"?

I no longer know what your criteria is. Are you now saying that you only want to use the first 2 characters of Column G in your criteria?

Something like this?

=SUMPRODUCT(--(I$5:I$9="Hired"),--(E5:E9<>""),--(LEFT(G5:G9,2)<>"LM"))

Yes - just the first 2 characters. Thank you!

Does "Thank you" mean that you tried my latest suggestion and that it works?

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History