Still need help with IF and Count Function

V

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")*(D2:D11=>1000),D2:D11))
Please help.

Is countif the right formula?
 
S

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")*(D2:D11=>1000),D2:D11))
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.
 
V

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

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))
 
P

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
 
P

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
 
S

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.
 
V

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.
 
V

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?
 
P

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top