Help with a formula/function, if it's possible

**RebeccaG** · 03-06-2013, 05:27 PM

I have 31 worksheets/tabs for each day of the month and a "Totals" sheet/tab. This is used to track admissions and discharges for an inpatient unit. What I monitor or track on the totals page is where patient's come from, time of day they are admitted and discharged ect. Now they would like me to track how many admits and discharges happen on specific days of the week.

I cannot figure out a formula to do this. Can this even be done? I have a formula on each sheet that has the total number of admits for that day (cell F33) and total number of dischages (cell K33). Also, I have the date in B4 of each sheet (formatted as long date: dddd, mmmm, dd, yyyy).

Is there formula I can put on my "Totals" sheet that would add all F33 cells only when that sheet/tab is a Monday and then do for each other day of the week????

Appreciate any help!

**pipoliveira** · 03-06-2013, 05:35 PM

Yes, it is possible.

You will need to use the COUNTIFS formula.

COUNTIFS will account based on various criterias and as you mentioned that they are all on the same cell reference is easy to do.

I can understand that your Workbook is confidential but can you attach an example with empty data?

With that exmaple file, I could create the formula you need and then you could just use it on your actual one.

Best regards,
Filipe

**RebeccaG** · 03-07-2013, 12:01 PM

Hi Filipe. Thanks for help! Attached is the workbook - I put in generic data for the first 10 days - I hope that is enough to work with.

**pipoliveira** · 03-08-2013, 03:21 AM

Hi Rebecca,

I thought you had the day of the week on each sheet along with the date, but that's ok.

It gave me a bit more work to do.

As you do not have the Weekday, the formula COUNTIFS will not work for this.

Instead I used the COUNTA formula that sums all cells that are not empty for a certain range. The formula was made to sum the sheets that belongs to the weekday (e.g. For Monday: days 7; 14; 21 & 28) and so on for the rest of the week.

Have a look on the file attached and please let me know if you need further clarification.

Best regards,
Filipe