Still need help with IF and Count Function
 

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?

Still need help with IF and Count Function
 

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


--
__________________________________
HTH

Bob

"Vicki" wrote in message
...

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?

Still need help with IF and Count Function
 

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.

Still need help with IF and Count Function
 

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?

"Stephen Lloyd" wrote:

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.

Still need help with IF and Count Function
 

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

--
Biff
Microsoft Excel MVP


"Vicki" wrote in message
...
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?

"Stephen Lloyd" wrote:

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.

Still need help with IF and Count Function
 

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

Hope this helps.

Pete

On Jul 28, 10:46*pm, Vicki wrote:
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?



"Stephen Lloyd" wrote:
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.- Hide quoted text -

- Show quoted text -

Still need help with IF and Count Function
 

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

On Jul 28, 11:11*pm, Pete_UK wrote:
Check that E6:E52 does not have any extra spaces at the end of the
text or in the middle.

Hope this helps.

Pete

On Jul 28, 10:46*pm, Vicki wrote:



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?

"Stephen Lloyd" wrote:
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.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Still need help with IF and Count Function
 

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.


"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Vicki" wrote in message
...
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?

"Stephen Lloyd" wrote:

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.

Still need help with IF and Count Function
 

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.

"Stephen Lloyd" wrote:

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.


"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Vicki" wrote in message
...
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?

"Stephen Lloyd" wrote:

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.

Still need help with IF and Count Function
 

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?

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Vicki" wrote in message
...
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?

"Stephen Lloyd" wrote:

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.

Still need help with IF and Count Function
 

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

On Jul 29, 5:33*pm, Vicki wrote:
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?



"T. Valko" wrote:
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))

--
Biff
Microsoft Excel MVP

"Vicki" wrote in message
...
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?

"Stephen Lloyd" wrote:

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.- Hide quoted text -

- Show quoted text -

Still need help with IF and Count Function
 

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.

--
Biff
Microsoft Excel MVP


"Stephen Lloyd" wrote in message
...
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.


"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Vicki" wrote in message
...
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?

"Stephen Lloyd" wrote:

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.