# Can anybody tell me why this formula doesn't work?

#### silvertears

##### New Member
=COUNTIF('January!B3:AF3,February!B3:AF3,March!B3:AF3,April!B3:AF3,May!B3:AF3,June!B3:AF3,July!B3:AF3,August!B3:AF3,September!B3:AF3,October!B3:AF3,November!B3:AF3,December!B3:AF3,B1)

I am trying to get it to count cells that match cell B1 on the end there on those 12 sheets (1 for each month). It doesn't want to do more than one sheet at a time though. Got any ideas apart from doing each sheet separately and than adding those up?

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### vbsteve

##### Board Regular
Hi

Try this just amended the ranges as needed.

=SUMPRODUCT(--(A3:A13=B1))+SUMPRODUCT(--(Sheet2!A3:A12=Sheet1!
B1))

Steve

#### Brian from Maui

##### MrExcel MVP
silvertears said:
=COUNTIF('January!B3:AF3,February!B3:AF3,March!B3:AF3,April!B3:AF3,May!B3:AF3,June!B3:AF3,July!B3:AF3,August!B3:AF3,September!B3:AF3,October!B3:AF3,November!B3:AF3,December!B3:AF3,B1)

I am trying to get it to count cells that match cell B1 on the end there on those 12 sheets (1 for each month). It doesn't want to do more than one sheet at a time though. Got any ideas apart from doing each sheet separately and than adding those up?

Try,

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!B1"),B3:AF3))

Where Sheets is a named range housing your sheet names.

#### Richard Schollar

##### MrExcel MVP
Silver Tears

Maybe:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&TEXT(DATE(2005,ROW(INDIRECT("1:12")),1),"mmmm")&"'!B3:AF3"),B1))

Which doesn't require a named range as per Brian's suggestion, but does assume there are 12 sheets named after the months (full names eg "January" not "Jan").

Best regards

Richard

#### silvertears

##### New Member
Thanks a million!!!

Thanks, for everyone's help. I ended up using Parsnip's, it was just easier! Even *I* would never guess I had my expert certification in Excel. It's been so long since I worked with it, I think I have forgotten EVERYTHING! I appreciate the help!

Silver

Replies
2
Views
50
Replies
6
Views
57
Replies
1
Views
73
Replies
2
Views
49
Replies
1
Views
141