I have monthly worksheets that use drop down boxes to pick criteria from
lists. The linked cell outputs a number based on what the chosen criteria was
in the drop down box. Currently I use this outputted number on a summary
sheet with a COUNTIF formula ie COUNTIF(Sept!$R$3:$R$200,2) to summarize
monthly info. I would like to generate yearly info instead without having to
generate monthly totals first. Is there a formula that can use both ranges -
Jan:Dec and $R$3:$R$200 - with the criteria of the linked cell ie. 2?

If you format your sheet names to three letters, for example Jan, Feb,
Mar, etc., you can use the following formula...

=SUMPRODUCT(COUNTIF(INDIRECT(TEXT(DATE(2005,ROW(IN DIRECT("1:12")),1),"mmm
")&"!R3:R200"),2))

or

=SUMPRODUCT(COUNTIF(INDIRECT(TEXT(DATE(2005,ROW(IN DIRECT(A1&":"&B1)),1),"
mmm")&"!R3:R200"),2))

....where A1 contains your starting month number, such as 1 for Jan, and
B1 contains your ending month number, such as 9 for Sep. Alternatively,
you can use the following formula...

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$C$1:$C$12&"'!R3: R200"),2))

....where C1:C12 contains your sheet names.

Hope this helps!

In article ,
"Overworked&Underpaid"
wrote:

I have monthly worksheets that use drop down boxes to pick criteria from
lists. The linked cell outputs a number based on what the chosen criteria was
in the drop down box. Currently I use this outputted number on a summary
sheet with a COUNTIF formula ie COUNTIF(Sept!$R$3:$R$200,2) to summarize
monthly info. I would like to generate yearly info instead without having to
generate monthly totals first. Is there a formula that can use both ranges -
Jan:Dec and $R$3:$R$200 - with the criteria of the linked cell ie. 2?