Formula Problem

[mac]

Hello,

I am having trouble with this formula

If WS A cell C75:C83>0, then WS S-3,II cell D17 should be > 0. If true, "No
variance". If false, "Must enter hours for reported salaries"

Any help will be greatly appreciated

Thank you

 

[T. Valko]

If WS A cell C75:C83>0

Does that mean *every* cell in the range is >0 or *any* cell in the range is

 

[mac]

Hi,
If any cell in the range has a number in it. Thank you for replying to my
question.

 

[T. Valko]

Try this (all on one line):

=IF(AND(COUNTIF('WS A'!C75:C83,">0"),
COUNTIF('WS S-3,II'!D17,">0")),"No variance",
"Must enter hours for reported salaries")

 

[mac]

Hi,

I thank you for answering my question. Can I change count to sum? the cells
are all numeric.

 

[T. Valko]

Can I change count to sum? the cells are all numeric.

It depends on what condition you're testing for and what the possible cell
values can be.

You said if *any* cell is >0 so a COUNTIF is the logical function to test
for that condition. How are you thinking about applying a SUM function as
the test? If there can be negative numbers in the range then a SUM of 10
and -10 equals 0.

This is why it's important to let us know *all* the details of a problem
when you post a question. We can only suggest something based on our "best
guess" of what your data might be unless you explicitly tell us.

 

[mac]

Hi
What I am trying to do is:
WS A has salaries
WS s3,11 has hours

What I want is to make sure that if there are salaries on Ws A that there
are hours to correspond with it on S-3ii

Does this make sense?
thank you mac

 

[T. Valko]

OK, since there's no such thing as a negative salary you could use a SUM
comparison. And, if the cells in WS S-3,II will only contain numbers (or
time values which are really just numbers formatted to look like times):

=IF(AND(SUM('WS A'!C75:C83),
'WS S-3,II'!D17>0),"No variance",
"Must enter hours for reported salaries")

 

[mac]

THANK YOU SO MUCH
--
thank you mac

OK, since there's no such thing as a negative salary you could use a SUM
comparison. And, if the cells in WS S-3,II will only contain numbers (or
time values which are really just numbers formatted to look like times):

=IF(AND(SUM('WS A'!C75:C83),
'WS S-3,II'!D17>0),"No variance",
"Must enter hours for reported salaries")

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

THANK YOU SO MUCH