Select Quarter for COUNTIFS

[garyca]

My database has a column for initial date from which Iidentify the YEAR and QUARTER. I use the COUNTIFS function where one criteriais QUARTER and the range is 3 for Q3. IfI want to look at the data for another QUARTER like Q1 to Q3, I have to changethe range to =<3. The report has over30 COUNTIFS lines so changing theformula from 3 to =<3 is a pain but doable. But for the other managers,they can’t make this change because the document is protected. How can I add adrop box, check box, etc so the managerscan select the QUARTER(S) and the COUNTIFS equations change accordingly?<o></o>

 

[sheetspread]

The pivot table does this more easily than formulas

 

[Gerald Higgins]

Hi, welcome to the board.

The criteria for COUNTIFS can be stored by themselves in a cell, for example

=countifs(a1:a10,$b$1,........and so on.

 

[garyca]

Thank you.I use pivot tables extensively but not for this situation. The only variable I want to change is the quarter. Can a selection from a drop box change part of a countifs formula?

 

[garyca]

Thank you. This may be the answer. For quarters 1 2,3 and 4 this works but if I want Q1-Q3 then the criteria is =<3. Will your approach still work?

 

[sheetspread]

put "<="&B1 as the condition, etc.