Still need help with IF and Count Function

[Vicki]

I tried the formulas
=sumproduct(--(sheet1!f6:f52<="54"),--(sheet1!g6:g52<="9"))
=Countif(sheet1!f6:f52<=54)-Countif(sheet1!g6:g52<=9)
=(countif(sheet1!f6:f52,"<=54")>0*(countif(sheet1!f6:52,"<=9")>-)
and neither function produces the correct answer for me, which is 20.

I have two conditions also. I want my formula to compare range f6:f52 and
g6:g52 on sheet1 for employees who are <=54yo with <=9 yrs of service and
place my answer which should be 20 on sheet2 b3.

I have found in Help the following formula that is what I want to do;
however, it produces 0 for me.
=COUNT(IF((B2:B11="Suyama")*(D211=>1000),D211))
Please help.

Is countif the right formula?

 

[Bob Phillips]

=sumproduct(--(sheet1!f6:f52<=54),--(sheet1!g6:g52<=9))

 

[Stephen Lloyd]

Good Afternoon,

I tried the formulas
=sumproduct(--(sheet1!f6:f52<="54"),--(sheet1!g6:g52<="9"))

This isn't working because your numbers of 54 and 9 are in quotes. Remove
them and the formula will work.

=SUMPRODUCT(--(A6:A52<=54),--(B6:B52<=9))

I have found in Help the following formula that is what I want to do;
however, it produces 0 for me.
=COUNT(IF((B2:B11="Suyama")*(D211=>1000),D211))

This formula will work but must be entered as an array using Ctrl+Shift+Enter

Using the SUMPRODUCT formulat this is rendered
=SUMPRODUCT(--(F2:F11="Suyama"),--(G2:G11>=1000))

Is countif the right formula?

Personally, I would choose the sumproduct formula.

 

[Vicki]

thank you both. removing the quotes made the function work. Another
problem: using the function below, I added two more columns to compare;
however that returned 0. for instance:

=SUMPRODUCT(--(budget_fin!F6:F52<=54),--(budget_fin!E6:E52="Budget
Analyst"),--(budget_fin!E6:E52="Accounting Analyst"),--(budget_fin!G6:G52<=9))

this should give me a return of 3. What have I done wrong? Is there a
limit that would cause an answer of 0?

 

[T. Valko]

Try it like this:

=SUMPRODUCT(--(budget_fin!F6:F52<=54),--(ISNUMBER(MATCH(budget_fin!E6:E52,{"Budget
Analyst","Accounting Analyst"},0))),--(budget_fin!G6:G52<=9))

 

[Pete_UK]

Check that E6:E52 does not have any extra spaces at the end of the
text or in the middle.

Hope this helps.

Pete

 

[Pete_UK]

On second thoughts, E6:E52 can't contain both "Budget Analyst" and
"Accounting Analyst", which is the way you have written your formula.
Biff's approach sets up an OR condition for these two.

Pete

 

[Stephen Lloyd]

Keeping the same nomenclature I believe you could check for either "budget
analyst" or "Accountin Analyst" with:

=sumproduct(--(budget_fin!F6:F52<=54),--(budget_fin!E6:E52="Budget
Analyst")+--(budget_fin!E6:E52="Accounting Analyst"),--(budget_fin!G6:G52<=9))


But if you end up having a lot of OR conditions, Biff's approach will end up
being a shorter formula.

 

[Vicki]

Thank you gentlemen. Actually Stephen your formula worked. I must be doing
something wrong with Biff's because that still produced 0. But again, I
thank you all for your help.

 

[Vicki]

Biff,
My apologies. I said this formula did not work and in fact it did. Thanks
so much.

Now that I have counted the titles that I wanted, how do I write a formula
that returns the count of those titles that are not accounting analyst and
budget analyst and within the different age groups?

 

[Pete_UK]

You could do it like this:

=SUMPRODUCT(--(budget_fin!F6:F52<=54),--(budget_fin!E6:E52<>"Budget
Analyst"),--(budget_fin!E6:E52<>"Accounting Analyst"),--(budget_fin!
G6:G52<=9))

The symbol <> means not equal to.

Hope this helps.

Pete

 

[T. Valko]

For an "or" condition:

=sumproduct(--(budget_fin!F6:F52<=54),(budget_fin!E6:E52="Budget
Analyst")+(budget_fin!E6:E52="Accounting Analyst"),--(budget_fin!G6:G52<=9))

However, ISNUMBER(MATCH is more efficient.