Summing across workbook

minekopanda

New Member
Joined
Oct 10, 2014
Messages
4
Hello,

I've tried several different formulas to get the results that I need but unfortunately none of them are working.

In this workbook we have a sheet that contains the report of all the gathered data then we have several sheets that contain the data that needs to be gathered/calculated.

An example of something we have that is currently working for the first bit of data that we need:

=COUNTIF('Canyon Springs'!B13:B17, "Y")

But now we need to combine info on the same sheet into our Stats sheet:

=COUNTIF('Canyon Springs'!B13:B17, "Y") AND =COUNTIF('Canyon Springs'!C13:C17, "MC*")
How can I combine the above to produce all the clients that have MC and are Ys?

The next step in our data is to now get a grand total of the above information across all the worksheets
example:
=COUNTIF('Canyon Springs'!B13:B17, "Y") And =COUNTIF('Canyon Springs'!C13:C17, "Y")
+
=COUNTIF('Wiley'!B13:B17, "Y") And =COUNTIF('Wiley'!C13:C17, "Y")
+
=COUNTIF('Hart'!B13:B17, "Y") And =COUNTIF('Hart'!C13:C17, "Y")

etc etc etc
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Welcome to the board.

=COUNTIF('Canyon Springs'!B13:B17, "Y") AND =COUNTIF('Canyon Springs'!C13:C17, "MC*")
How can I combine the above to produce all the clients that have MC and are Ys?

If you have XL2007+ you can use countifS
=COUNTIFS('Canyon Springs'!B13:B17, "Y",'Canyon Springs'!C13:C17, "MC*")


then once you have one of those for each sheet, to get the grand total just add them together.
Say you put those countifs functions for each sheet in a range say A1:A10
Then you can just do =SUM(A1:A10)

Hope that helps.
 

minekopanda

New Member
Joined
Oct 10, 2014
Messages
4
I have excel 2010. What if I don't have a sheet with all those totals...
Is there a way then to have them all add up together or would I have to have that breakdown?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
I would recommend creating 1 formula for each individual sheet, then sum the results of those.

I can't imagine wanting to know the sum of all the sheets in one formula, but not ALSO want to know the sum of each individual sheet by themselves.
 

minekopanda

New Member
Joined
Oct 10, 2014
Messages
4

ADVERTISEMENT

Well originally that was the plan but now they just want a grand total without seeing the subtotals, but I guess I could hide that sheet.

Another question I have is, a lot of this data is based on months, I've searched through google and there seems to be different methods to this as well.
The data chart is broken up into months for the fiscal year. There's certain information that needs to be included only if it falls within the specified month.
How would I go about doing that on each sheet?

Example
- Referrals
Row E19:E65 contain the date they were referred.
I need it to tell me in the Jan box how many, feb how many etc.

What would be the most efficient way to do this?


Thank you so much for your help!
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Well originally that was the plan but now they just want a grand total without seeing the subtotals, but I guess I could hide that sheet.
Typical upper management stupidity.
Yes definately do the sums seperately and only show them the overall sum.
I'd bet the farm (well not literally) that within a month they'll be back saying, "Hey, we'd like to see the individal sums"


That sounds like a new issue, and charts aren't really in my skill set.
I'd make a new thread for that.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,040
Messages
5,526,409
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top