calculation problem...
 

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?

calculation problem...
 

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!AU 1: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

"Dagonini" wrote in message
oups.com...
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?

calculation problem...
 

Try =SUMPRODUCT(--(Sheet1!A1:A1145),--(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 V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dagonini" wrote in message
oups.com...
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?

calculation problem...
 

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!

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bernard Liengme" wrote in message
...
Try =SUMPRODUCT(--(Sheet1!A1:A1145),--(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 V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dagonini" wrote in message
oups.com...
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?

calculation problem...
 

Dagonini wrote:
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?