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

silvertears

New Member
Joined
Aug 27, 2004
Messages
4
=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?
 

Some videos you may like

Excel Facts

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

vbsteve

Board Regular
Joined
Dec 11, 2003
Messages
99
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
Joined
Feb 16, 2002
Messages
8,458
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
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Aug 27, 2004
Messages
4
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,081
Messages
5,570,086
Members
412,310
Latest member
mark884
Top