» Counting the Number of Values from Multiple Worksheets that Match Criteria
CATEGORY - Excel Counting
VERSION - All Microsoft Excel Versions
Counting the total number of values from the range A1:B3 on Sheets 1, 2 & 3 that are greater than the criteria specified in cell A2.
Solution:
Use the SUM, COUNTIF, and INDIRECT functions as shown in the following formula:
=SUM(COUNTIF(INDIRECT("Sheet"&{1,2,3}&"!A1:B3"),">"&A2))
Book Store:
Recommended Books:
- Rich Dad, Poor Dad: What the Rich Teach Their Kids About Money--That the Poor and Middle Class Do Not!
- Managing by the Numbers: A Commonsense Guide to Understanding and Using Your Company's Financials: An Essential Resource for Growing Businesses
- Marketing Plans That Work, Targeting Growth and Profitability
- Microsoft Windows XP Registry Guide
- The Intelligent Investor: The Classic Bestseller on Value Investing
- Business Analysis and Valuation: Using Financial Statements, Text and Cases
Re: Counting the Number of Values from Multiple Worksheets that Match Criteria
DonkeyOte
[I]Your post does not comply with Rule [B]2[/B] of our Forum [/I][URL="http://www.excelforum.com/forum-rules/642590-forum-rules.html"][COLOR=#800080]RULES[/COLOR][/URL]. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.Also, please ensure you post in a relevant forum ... this thread is now closed.
I tried to take the formula:
=SUM(COUNTIF(INDIRECT("Sheet"&{1,2,3}&"!A1:B3"),">"&A2))
and change it for my specific situation but it comes back as an error.
=SUM(COUNTIF(INDIRECT("Sheet"&{'6-24'!:LAST!}&"!C4:O4"),"&1-a))
6-24 is the name of the first sheet which will never change and LAST is the name of the last sheet which will never change. C4:O4 are the range of cells on each sheet that I would like to analyze and I want to count how many times it say 1-a in those cells.
Can anyone tell me where I'm going wrong?