SUMIFS returning #VALUE!

**Lifeseeker** · 12-06-2011, 03:52 PM

Hello,

Please see the attached. I would like to know why the sumifs formuale is not working....it was working on another copy....

The idea here is to calculate the mean in col F in "new CCM clinic reporting" worksheet.

I am using a named range as well, but it's just not working...

Formula I used is:

Please Login or Register  to view this content.

Could anybody help please?

Thank you

**NBVC** · 12-06-2011, 04:13 PM

In the named ranges for the Mean columns.. change the COUNTA function to COUNT function so that it counts numbers and offsets by same amount as the text columns...

e.g. for VGHClinicMean: =OFFSET('VGH Clinic Intake'!$P$2,0,0,COUNT('VGH Clinic Intake'!$P:$P))

unless you give headings to the other columns too....

Then you would need to -1 from the COUNTA:

=OFFSET('VGH Clinic Intake'!$P$2,0,0,COUNTA('VGH Clinic Intake'!$P:$P)-1)

**Lifeseeker** · 12-07-2011, 05:08 PM

Originally Posted by NBVC

In the named ranges for the Mean columns.. change the COUNTA function to COUNT function so that it counts numbers and offsets by same amount as the text columns...

e.g. for VGHClinicMean: =OFFSET('VGH Clinic Intake'!$P$2,0,0,COUNT('VGH Clinic Intake'!$P:$P))

unless you give headings to the other columns too....

Then you would need to -1 from the COUNTA:

=OFFSET('VGH Clinic Intake'!$P$2,0,0,COUNTA('VGH Clinic Intake'!$P:$P)-1)

Hi, thank you.

I thought COUNTA counts non-null values while COUNT includes null values? What are implications of just going with the original COUNTA without the "-"?

Thanks

**NBVC** · 12-08-2011, 08:59 AM

COUNTA only counts null values if the nulls are results of formula, e.g. =IF(A1=1,1,""). If you don't include the -1 then a the range includes one blank cell at the bottom, since you started at row 2 and added the COUNTA() result.