Sumifs formula with a twist

**Cassi** · 01-25-2013, 07:23 PM

Is there a way to make a sumifs formula that still works if one of the criteria is blank in some cases.

=SUMIFS('Indirect Rate Plan Analysis'!$F:$F,'Indirect Rate Plan Analysis'!$D:$D,$D17,'Indirect Rate Plan Analysis'!$L:$L,$C17,'Indirect Rate Plan Analysis'!$G:$G,E$15)

Sometimes C17 is blank

Thanks

**rcm** · 01-25-2013, 07:28 PM

=if(c17<>"",SUMIFS('Indirect Rate Plan Analysis'!$F:$F,'Indirect Rate Plan Analysis'!$D:$D,$D17,'Indirect Rate Plan Analysis'!$L:$L,$C17,'Indirect Rate Plan Analysis'!$G:$G,E$15)
,sumifs(criterias to be eveluated if c17 is ""))

what criteria is there to use if c17 is blank?

**Cassi** · 01-25-2013, 07:33 PM

Nothing, this is pulling from a pivot table and sometimes that cell is blank because it a subtotal line. I still need it to calculate as if it did or did not have something in that cell.

**rcm** · 01-25-2013, 07:54 PM

so formulate the sumifs() without the c17 matching criteria it in the criteria part if c17 is blank

**Cassi** · 01-25-2013, 08:10 PM

Im sorry I dont understand what you mean.

**rcm** · 01-26-2013, 10:22 AM

=if($c17<>"",SUMIFS('Indirect Rate Plan Analysis'!$F:$F,'Indirect Rate Plan Analysis'!$D:$D,$D17,'Indirect Rate Plan Analysis'!$L:$L,$C17,'Indirect Rate Plan Analysis'!$G:$G,E$15)
,SUMIFS('Indirect Rate Plan Analysis'!$F:$F,'Indirect Rate Plan Analysis'!$D:$D,$D17,'Indirect Rate Plan Analysis'!$G:$G,E$15))

**Tony Valko** · 01-26-2013, 10:42 AM

What type of data is in 'Indirect Rate Plan Analysis'!$L:$L?

Is it text? Numeric? Could be both? Something else?

Are there any empty cells in 'Indirect Rate Plan Analysis'!$L:$L?