calculation problem...

  • Thread starter Thread starter Dagonini
  • Start date Start date
D

Dagonini

I have a formula that I am using to transfer data from one spread sheet
to another.
=IF(Sheet1!AU2:AU11 >=45,(COUNTIF(Sheet1!AU2:AU11,"<=49")),"")

AU 1 through 11 looks like this:

0
37
47
57
67
27
37
37
37
37
37

I am asking the formula to count how many are between 45 & 49. The
answer keeps telling me 8. But it should be 1. What am I doing wrong?
 
I'm sure there's another way, but one way you could do it.
Your original formula is looking at AU2:AU11, but yousay that you have 11
values from AU1:AU11. I've adjusted that in the formula below.

=COUNTIF(Sheet1!AU1:AU11,">=45")+COUNTIF(Sheet1!AU1:AU11,"<=49")-11

The "-11" at the end of the formula is the number of number of values that
you are searching. If it is not known what the number of values will be,
you could add a CountA function at the end of the formula.

HTH,
Paul
 
Of course A1:A11 should be changed to AU2:AU11 in you case
I used A1:A11 to check the formula was working with no typos!
 
Dagonini said:
I have a formula that I am using to transfer data from one spread sheet
to another.
=IF(Sheet1!AU2:AU11 >=45,(COUNTIF(Sheet1!AU2:AU11,"<=49")),"")

AU 1 through 11 looks like this:

0
37
47
57
67
27
37
37
37
37
37

I am asking the formula to count how many are between 45 & 49. The
answer keeps telling me 8. But it should be 1. What am I doing wrong?
Click to expand...
Does the second half of the if statement not just return the number of
cells less than or equal to 49?
 
Back
Top