Using multiple functions in one cell
Matthew Harrington 
I'm using Excel 2010 and I'm trying to do the following
- For IT Changes in different locations, I'm trying to find how many "successful" changes in "Lake Mary"
- The data is in the same file using different spreadsheets
EX:
Page: By location
NewJersey NewYork Conn% of successful [formula] [formula] [formula]
Page: Aug
Date Location Risk Level Status 7/20/2015 NewJersey 1 Successful 7/30/2015 NewYork 1 Successful 7/30/2015 NewYork 1 Successful
I hope this was enough information. Which functions can I use? Thank you
1 Answer
So as I understand it you have a sheet "Aug" with a table in it, let's say Date is column A, Location is column B, Risk Level is column C and Status is column D.
On another sheet you want a summary of this information by location.
Using formulas, suppose your locations are in cells B1, C1, D1, etc. and you want to put the information from sheet "Aug" in the row beneath them. In cell B2, use the formula
=COUNTIFS(Aug!$B:$B, B$1, Aug!$D:$D, "Successful")to count the number of rows in the "Aug" sheet with the location specified in cell B1 of the summary sheet and status "Successful". Then you can fill this formula right to populate data for the other locations.
If you have other data sheets and you want to summarise by sheet as well as location, you can put the sheet names in column A (so cell A2 would be "Aug") and amend the formula like this:
=COUNTIFS(INDIRECT("'"&$A2&"'!$B:$B"), B$1, INDIRECT("'"&$A2&"'!$D:$D"), "Successful")This uses the value in cell A2 to construct the addresses that it looks for in the other sheets.
If you only have one data sheet, then there's an alternative: create a PivotTable from your data table, with the Location field in the column and the Status field in the row.
