SUMIF on a Calculated Field

T

Trevor Aiston

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
 
T

Teethless mama

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
 
S

Shane Devenshire

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top