Index match count

[Keelin]

Hi All,

I think this should be simple to solve, I have a calendar (as per the spreadsheet attached) and I would like to count the number of days in a chosen month. So index match for e.g. January and Wednesday and count if there is a number in the cell.

As per the attachment there are 4 wednesdays in January, so thats what I would like to be able to get to.

But not sure how to put multiple index match, countif and isnumber together!....or get a sumproduct to work

The purpose of the spreadsheet is child enrollments in pre-school for tax rebate analysis when a child is only going a number of days a week.....for me its important to be able to calculate with accuracy

Any help appreciated

Thanks so much

K

[protonLeah]

Named ranges used:

Please Login or Register  to view this content.

Formulas in L6:P17:

Please Login or Register  to view this content.

Day names in L2:P2 appended with "s" to correspond to the named ranges

[FlameRetired]

Similar to Ben's but without named ranges.

Array-entered in L6 filled down and across.

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

[Keelin]

That is wonderful help thank you soo very much

I ended up using the single formula as that enabled me to put multiple enrollments on the single spreadsheet.

The second part to this is how to count the public holidays for each month depending on enrollment days.

For example, in the spreadsheet one child is enrolled on tuesdays and fridays. There are 2 public holidays in January that fall on these days and I would like to be able to count them depending on enrollment days which would feed into a separate cost spreadsheet.

As per the spreadsheet below.

The reason is that public holidays do not have a tax rebate and are double the cost of 'normal' days which receive a government rebate so its important to be able to calculate with accuracy.

Please feel free to suggest better ways of structuring the public holiday table if needs be.

Many thanks

K