Super long countif divided by super long countif

[tristandoo]

Could someone please let me know if it is possible to shrink this countif statement? It becomes too much to handle since I have to copy it for multiple different rows. The A1 stuff is to know if the sheet is "active" (if there is a different way to do this I am all ears) it would need to stay the same. The "B10" is what changes for different rows/people.
=(COUNTIF(Jan.!B10,Jan.!B10)+COUNTIF(Feb.!B10,Feb.!B10)+COUNTIF(Mar.!B10,Mar.!B10)+COUNTIF(Apr.!B10,Apr.!B10)+COUNTIF(May!B10,May!B10)+COUNTIF(Jun.!B10,Jun.!B10)+COUNTIF(Jul.!B10,Jul.!B10)+COUNTIF(Aug.!B10,Aug.!B10)+COUNTIF(Sep.!B10,Sep.!B10)+COUNTIF(Oct.!B10,Oct.!B10)+COUNTIF(Nov.!B10,Nov.!B10)+COUNTIF(Dec.!B10,Dec.!B10)+COUNTIF('Yr. Plan Mtg'!B10,'Yr. Plan Mtg'!B10))
/ (COUNTIF(Jan.!A1,Jan.!A1)+COUNTIF(Feb.!A1,Feb.!A1)+COUNTIF(Mar.!A1,Mar.!A1)+COUNTIF(Apr.!A1,Apr.!A1)+COUNTIF(May!A1,May!A1)+COUNTIF(Jun.!A1,Jun.!A1)+COUNTIF(Jul.!A1,Jul.!A1)+COUNTIF(Aug.!A1,Aug.!A1)+COUNTIF(Sep.!A1,Sep.!A1)+COUNTIF(Oct.!A1,Oct.!A1)+COUNTIF(Nov.!A1,Nov.!A1)+COUNTIF(Dec.!A1,Dec.!A1)+COUNTIF('Yr. Plan Mtg'!A1,'Yr. Plan Mtg'!A1))

 

[Andrew Poulsom]

Welcome to MrExcel.

You can use a 3D reference with COUNTA:

=COUNTA(Jan.:Dec.!B10)

 

[tristandoo]

WOW! that really cleared things up there A LOT! Thank you so much! I will be using this formula more often now. Thanks a ton!

 

[tristandoo]

Well that sure did shorten it up but now is there a way to shorten the "Active" stuff?

 

[Andrew Poulsom]

Do you mean?

=COUNTA(Jan.:Dec.!A1)

 

[tristandoo]

Sorry my brain must not be thinking things through as much at 4AM. Yes that would work, and I really don't know why I didn't put 2 and 2 together to get that. Is there a way to be able to drag down and it copy down to other rows? Every time I do that it changes the A value and the B value. I need it to change only the B value. I tried setting up the pattern but it didn't work and if I just do one it switches between A1 and A2.

 

[Andrew Poulsom]

Like this?

=COUNTA(Jan.:Dec.!A$1)

 

[tristandoo]

well I just recently watched something on that and didn't really think to apply it here. Plus this would be the first time I have actually used $ to lock a value. My next question is off topic but do you know a way to post the date(7/29/13) of the last monday of every month in a cell by formula or something else?

 

[tristandoo]

Let me make that a bit clearer:
I would like each work book to have the date of the last monday of its month posted in a cell. So Jan.! would have the last monday in Jan. posted in a call and Feb! would have the last monday in Feb posted in a cell etc. (some months will be different years)

 

[Andrew Poulsom]

To return the last Monday of January 2013:

=DATE(2013,2,0)-WEEKDAY(DATE(2013,2,0),2)+1