Suming an ongoing waiting list

Hi there,

Please could someone offer me assistance with the following formula. I have a database for the year and one of the things I keep track of monthly is patients who do not yet have an appt date and are on a waiting list.

If you look at the attachment you can see that the cell becomes red for any patients who are on the waiting list. I would like to track the ongoing waiting list, which would include patients still on the waiting list from previous months on different worksheets.

For example, the ongoing waiting list count for March would include march, feb and Jan.
For feb, it would include feb and Jan. And so on for the rest of the months of the year.

Please could you show me how to compose a formula that would count the current month waiting list and that of the previous months...hope this makes sense?!

Thanks,

Sophy

1) Add a blank worksheet to your workbook.
2) Name it FIRST and put it at the beginning of all sheets
3) Hide this sheet, it will always be before sheet JAN this way.

4) Add a blank worksheet to your workbook.
5) Name it LAST and put it at the END of all sheets
6) Hide this sheet, it will always be after the last visible sheet, even if you add more

The formula that will give you a count for all entries in column J on all sheets in between First and Last is:

=COUNT(First:Last!J:J)

The attached wb shows this, all that is left is for you to hide the added "boundary" sheets.

For counting colored cells? No.

That may be available in the latest versions of Excel, but not ours.

Something like:

=SUMPRODUCT(--($A$4:$A$100<>""), --($J$4:$J$100=""))

Since columns are free, let's add one more to make this simple.

On your monthly sheets, add a new column O called "waiting". In O4 add this formula and copy down as far as needed:

=IF(AND(A4<>"", J4=""),1,"")


Now your 3D sum formula will simply be: =SUM(First:Last!O:O)