calculation problem...

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

 

[PCLIVE]

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

 

[Bernard Liengme]

Try =SUMPRODUCT(--(Sheet1!A1:A11>45),--(Sheet1!A1:A11<49))
For explaination see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes

 

[Bernard Liengme]

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!

 

[Hakk]

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?

Does the second half of the if statement not just return the number of
cells less than or equal to 49?