sumifs criteria

**Wrighty** · 10-22-2010, 04:39 AM

Hello All

Is it possible to sum a range of criteria using SUMIFS?

Many Thanks.

**Marcol** · 10-22-2010, 04:48 AM

Try this, but why?

=SUMIFS(A1:A16,A1:A16,"<>0")

Hope this helps

**Wrighty** · 10-22-2010, 05:05 AM

I am trying to sum all of these

=SUMIFS('Data Table'!S$7:S$200,'Data Table'!$D$7:$D$200,"{3050,3100,3150,3250,3300,3350,3360,3400}",'Data Table'!$C$7:$C$200,"{52510,52518,52519}",'Data Table'!$A$7:$A$200,"""FNE")

**DonkeyOte** · 10-22-2010, 05:21 AM

Try the below - note array entry required

=SUM(SUMIFS('Data Table'!S$7:S$200,'Data Table'!$D$7:$D$200,{3050,3100,3150,3250,3300,3350,3360,3400},'Data Table'!$C$7:$C$200,TRANSPOSE({52510,52518,52519}),'Data Table'!$A$7:$A$200,"FNE"))
confirmed with CTRL + SHIFT + ENTER (enter alone will not suffice)

**daddylonglegs** · 10-22-2010, 07:08 AM

If you are "hard coding" the criteria values into the formula you could avoid TRANSPOSE by just using ; separators in the second array constant, i.e.

=SUM(SUMIFS('Data Table'!S$7:S$200,'Data Table'!$D$7:$D$200,{3050,3100,3150,3250,3300,3350,3360,3400},'Data Table'!$C$7:$C$200,{52510;52518;52519},'Data Table'!$A$7:$A$200,"FNE"))