Summing based on Filters – Excel 2007

Topic

New Reply

Howdy,

I have an accommodation management spreadsheet which I use to:
-book accommodation
-categorise the stay
-sum the charges based on category of stay.
– sum the number of stays based on the category

I can use the subtotal count function (F12) to count the number of stays based on the filter (typically date), however I cant work out how to count the number of nights for each individual category, eg “Non-Billable”, “Private”, or “Billable Special” (F13 to F17).

Any help would be greatly appreciated.

Thanks Claude

Reply | Quote

Replies

Do you mean if you filtered on, say, QUEEN and non-billable, you want to know how many show up?

If so, in F13, try: =SUBTOTAL(3,H24:H186) where 3 is COUNTA

Reply | Quote

what i am after, is that if I filter on a date range, eg February, then I would like to know how many nights were billable, non-billable, and billable special.

Reply | Quote

In F13, try: =SUMPRODUCT(SUBTOTAL(3,OFFSET(H24,ROW(H24:H186)-ROW(H24),0)),(H24:H186=”Non-Billable”)+0)

And, change the “type” accordingly for the other cells. Is this it?

Kevin

Reply | Quote