SUMIFS & Data Validation

[SChapman]

Hi,

I am using the formula below to sum in cell B8, when I insert a row after B7 the sum formula doesn't know to add this into the formula. Is there a way to do this?

Please Login or Register  to view this content.

Thanks

[AZ-XL]

You can put your data into table and reference table headers in formula. After that, whenever you will add data below b7 the table range will enlarge and reference will include it.

[ChemistB]

Here's one way

=SUMIF($C$5:OFFSET($C$8,-1,0),"paid Sickness",$C$5:OFFSET($B$8,-1,0))+SUMIF($C$5:OFFSET($C$8,-1,0),"Unpaid Sickness",$C$5:OFFSET($B$8,-1,0))

You also might want to look at dynamic named ranges
http://www.contextures.com/xlNames01.html#Dynamic

[SChapman]

I'll use tables, didn't think of that. Thanks for the responses guys!