Hello All . I have a dataset and would like to do the SumIfs based on couple criteria and make the table below. First Criteria would be the months that is shown in column F and then sum of number 1 for any other column based on that particular month in column F. For example in Feb 22 I have total of 20 forecast, and out of that, 17 of them are done already in 1(A), all 20 of those are done in 2(A) etc. Does anyone know how to figure this out?Data is on the left side in the sheet. Thanks
Not so much of a “sumifs” problem, but more of a “reshaping” and “aggregating” based on the date and 1A, 2A, etc. columns.
You would need to unpivot the table to start with to generate the sums by Region, date, and 1A, 2A, etc. columns, then re-pivot back into the format you show.
Use a separate (derived) table to generate the totals (“Count of F”) and merge that back in by Region, date (F), and move it into place.
Attached is the solution workflow to create your output. There is a modified version of your spreadsheet (Sheet1(2)), which removes your sample table. Unzip the files and keep them in the same folder and it will work automatically.
At the end of the process, I introduce a “lookup table” with month numbers you can merge in so the final product can be sorted by Region (I’m assuming you may have more than one region?) and Date (F), based on the month #.
All tables in the workflow have been annotated.
Hopefully, this helps. Let me know if I missed anything in this solution.
