I have created the formula =COUNTIFS('FEB
FY10'!$P$7:$P$126,"Australia",$BG$7:$BG$126,"= 100")/C19. The problem is
that cells BG7:BG126 contain a formula so the formula returns a value of 0 as
it does not see the results of the formula in BG7:BG126. How can I make this
work

Works OK for me. Maybe the cells don't exactly equal 100?

I would write this: "= 100" simply as 100:

=COUNTIFS('FEB FY10'!$P$7:$P$126,"Australia",$BG$7:$BG$126,100)/C19

--
Biff
Microsoft Excel MVP


"ottodesque" wrote in message
...
I have created the formula =COUNTIFS('FEB
FY10'!$P$7:$P$126,"Australia",$BG$7:$BG$126,"= 100")/C19. The problem is
that cells BG7:BG126 contain a formula so the formula returns a value of 0
as
it does not see the results of the formula in BG7:BG126. How can I make
this
work

I tried copying in the formala below and still got a value of 0 in my target
cell. Perhaps more detail is needed: 'Feb FY10'!$P$7 contains text ie
Australia, $BG$7 contains =IF(C70,(BD7)*-6.25+100,""), and C19 contains
=COUNTIF('FEB FY10'!$P$7:$P$126,"Australia"). Hopefully this will help

"T. Valko" wrote:

Works OK for me. Maybe the cells don't exactly equal 100?

I would write this: "= 100" simply as 100:

=COUNTIFS('FEB FY10'!$P$7:$P$126,"Australia",$BG$7:$BG$126,100)/C19

--
Biff
Microsoft Excel MVP


"ottodesque" wrote in message
...
I have created the formula =COUNTIFS('FEB
FY10'!$P$7:$P$126,"Australia",$BG$7:$BG$126,"= 100")/C19. The problem is
that cells BG7:BG126 contain a formula so the formula returns a value of 0
as
it does not see the results of the formula in BG7:BG126. How can I make
this
work

Try this formula:

=SUMPRODUCT(--('FEB FY10'!$P$7:$P$126="Australia"),--($BG$7:$BG$126=100))

If the result is 0 then there are either no rows where both conditions are
met or you have data problems.

There may be unseen characters like leading/trailing spaces in $P$7:$P$126.
$BG$7:$BG$126 may not exactly equal 100. The true value might be something
like 100.000005. You're using this formula in which you're doing
calculations with decimals.

=IF(C70,(BD7)*-6.25+100,"")

So how many of those formula return *exactly* 100?

--
Biff
Microsoft Excel MVP


"ottodesque" wrote in message
...
I tried copying in the formala below and still got a value of 0 in my
target
cell. Perhaps more detail is needed: 'Feb FY10'!$P$7 contains text ie
Australia, $BG$7 contains =IF(C70,(BD7)*-6.25+100,""), and C19 contains
=COUNTIF('FEB FY10'!$P$7:$P$126,"Australia"). Hopefully this will help

"T. Valko" wrote:

Works OK for me. Maybe the cells don't exactly equal 100?

I would write this: "= 100" simply as 100:

=COUNTIFS('FEB FY10'!$P$7:$P$126,"Australia",$BG$7:$BG$126,100)/C19

--
Biff
Microsoft Excel MVP


"ottodesque" wrote in message
...
I have created the formula =COUNTIFS('FEB
FY10'!$P$7:$P$126,"Australia",$BG$7:$BG$126,"= 100")/C19. The problem
is
that cells BG7:BG126 contain a formula so the formula returns a value
of 0
as
it does not see the results of the formula in BG7:BG126. How can I
make
this
work