SUMIF on a Calculated Field
 

I am trying to use a sumif function on a range of cells (named QUEST7) which
has calculated cells
Each calculated cell is uses the AND function
CELL AQ8 '=AND(AO8,AP8<5)'

The sum function is used to calculate the named range of cells
=SUMIF(QUEST7,1,North). North being a named range
But it returns 0 (actual result should be 12). I can get round it by
adding another column with a numeric result based on the AND function
column(=IF(AQ8=TRUE,1,0)). But I thought in excel TRUE=1, FALSE=0

Will sumif not sum 'TRUE' results?
Trevor

SUMIF on a Calculated Field
 

But I thought in excel TRUE=1, FALSE=0

TRUE is not equal 1, and FASE is not equal 0. In order to get '1' and '0'
you have to use math operator

example:
=AND(AO8,AP8<5) =TRUE

to convert to TRUE to '1'

=AND(AO8,AP8<5)*1 =1
=AND(AO8,AP8<5)+0 =1
=AND(AO8,AP8<5)^1 =1
=--AND(AO8,AP8<5) =1

=TRUE*TRUE = 1
=TRUE*FALSE =0
=FALSE*FALSE =0


"Trevor Aiston" wrote:

I am trying to use a sumif function on a range of cells (named QUEST7) which
has calculated cells
Each calculated cell is uses the AND function
CELL AQ8 '=AND(AO8,AP8<5)'

The sum function is used to calculate the named range of cells
=SUMIF(QUEST7,1,North). North being a named range
But it returns 0 (actual result should be 12). I can get round it by
adding another column with a numeric result based on the AND function
column(=IF(AQ8=TRUE,1,0)). But I thought in excel TRUE=1, FALSE=0

Will sumif not sum 'TRUE' results?
Trevor

SUMIF on a Calculated Field
 

Test for true

=SUMIF(QUEST7,TRUE,North)

--
__________________________________
HTH

Bob

"Trevor Aiston" wrote in message
...
I am trying to use a sumif function on a range of cells (named QUEST7)
which
has calculated cells
Each calculated cell is uses the AND function
CELL AQ8 '=AND(AO8,AP8<5)'

The sum function is used to calculate the named range of cells
=SUMIF(QUEST7,1,North). North being a named range
But it returns 0 (actual result should be 12). I can get round it by
adding another column with a numeric result based on the AND function
column(=IF(AQ8=TRUE,1,0)). But I thought in excel TRUE=1, FALSE=0

Will sumif not sum 'TRUE' results?
Trevor

SUMIF on a Calculated Field
 

What does your data look like?

I would expect =AND(AO8,AP8<5) to be returning TRUE or FALSE, not 0, 1

If so try

=SUMIF(QUEST7,TRUE,North)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Trevor Aiston" wrote:

I am trying to use a sumif function on a range of cells (named QUEST7) which
has calculated cells
Each calculated cell is uses the AND function
CELL AQ8 '=AND(AO8,AP8<5)'

The sum function is used to calculate the named range of cells
=SUMIF(QUEST7,1,North). North being a named range
But it returns 0 (actual result should be 12). I can get round it by
adding another column with a numeric result based on the AND function
column(=IF(AQ8=TRUE,1,0)). But I thought in excel TRUE=1, FALSE=0

Will sumif not sum 'TRUE' results?
Trevor