Posted: 6/4/2017 5:33:14 PM EDT
Normally I'm a learn as you go type, but this has got me stumped. Countifs maybe? I want to take this data:
And get this output: 
Count is the total# of transactions for that day, Count Prevented is the number canceled, and $Prevented is the value of the transactions canceled. |
|
The short answer is to use Countifs, and Sumifs.
Do you need the exact formulas, or can you figure it out from there? Edit: Here are my formulas: =COUNTIFS(D:D,M5,C:C,"Cancel") =SUMIFS(I:I,D:D,M5,C:C,"Cancel") Both my formulas say M5. That is where it is looking up the date. So the second table would have to have the date field as the lookup value in M5. (your output table cell A2) Just modify that bit to suit your needs, or change the formulas a bit and put the lookup on a new sheet. That makes more sense, but I didn't want to complicate the formals. |
If you paste all that raw data from the first screenshot into a post I can copy into Excel I can help... I'm way too lazy to re-type all that 
Also where is the raw data maintained? Do you just enter it all manually into that one Excel spreadsheet or is that data exported into Excel from another system? Depending how the data is stored could dictate which technique, pivot table vs using formulas, will be easier going forward. |
|
Quoted:
Cool. Try it on your own, then look at the edited post, and see if that helps a bit more. |
|
Quoted:
I looked into that. I couldn't figure out how to get a count of the # of transactions in a day. Quoted:
Quoted:
Use a pivot table |
|
Easy peasy pivot table...
Here's how... 1. Insert a column to the right of the Resolution column and name it Prevented (name should be in cell D1) 2. Paste the following formula into cell D2 in the column (you of course know that the cell references will change for each column). Copy it to all of the other cells in D. =IF(C2="Cancel",J2,FALSE) 3. Insert a pivot table Rows = Established Date Values [1] = Count on Analyst column Values [2] = Count Numbers on Prevented column Values [3] = Sum on Prevented column |
|
Quoted:
Easy peasy pivot table... Here's how... 1. Insert a column to the right of the Resolution column and name it Prevented (name should be in cell D1) 2. Paste the following formula into cell D2 in the column (you of course know that the cell references will change for each column). Copy it to all of the other cells in D. =IF(C2="Cancel",J2,FALSE) 3. Insert a pivot table Rows = Established Date Values [1] = Count on Analyst column Values [2] = Count Numbers on Prevented column Values [3] = Sum on Prevented column |