NoExpert - help with this sumproduct

October 12, 2010 at 05:16:50
Specs: Windows XP
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.


See More: NoExpert - help with this sumproduct

Report •

#1
October 12, 2010 at 06:44:33
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 1

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

Using this table, your formula returns 2:

Table 2

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

Are those results correct?


Report •

#2
October 12, 2010 at 06:54:38
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.

Report •

#3
October 12, 2010 at 07:43:47
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 3

	   E	   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


Report •

Related Solutions

#4
October 12, 2010 at 08:18:44
sorry yes it did and should have only returned 1

Report •

#5
October 12, 2010 at 08:29:25
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.


Report •

#6
October 12, 2010 at 08:43:04
Table 3 - table 1 and 2 did not return the correct results.

Report •

#7
October 12, 2010 at 09:39:31
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 why the 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 2

	  E	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 why the result should not be 2.



Report •

#8
October 12, 2010 at 09:58:07
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 Hired

The 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


Report •

#9
October 12, 2010 at 10:14:29
re: The result should be 1.

And that is exactly what your formula returns.

What's the problem?


Report •

#10
October 12, 2010 at 10:26:34
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!


Report •

#11
October 12, 2010 at 10:34:14
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"))


Report •

#12
October 12, 2010 at 10:49:43
Yes - just the first 2 characters.

Thank you!


Report •

#13
October 12, 2010 at 11:00:28
Does "Thank you" mean that you tried my latest suggestion and that it works?

Report •

Ask Question