Summing across workbook

[minekopanda]

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

 

[Jonmo1]

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]

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]

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]

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]

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.

 

[minekopanda]

LoL yeah...

Thank you so much for your help. So far the formula is working.