02-16-2020 03:09 PM
02-16-2020 03:09 PM
I am trying to use SUMIFS to register weekly totals from one worksheet to another...
For example, I have one worksheet that I manually enter data on week by week and i would like to automatically save the data on another sheet in categories ranging from week 1, week 2 etc...
so... I have managed thus far to save the data for week 1 (for example) using the formula =SUMIFS(Weekly!I20,Weekly!D2,"february",Weekly!H2,"1"). This works fine, but when I alter the criteria “Weekly!H2,"1" to Weekly!H2,"2", its erases the previous total...
This is hard to explain and I feel this makes no sense whatsoever, but hopefully someone out there can see through the madness...
02-16-2020 03:52 PM
Hello, based on the pictures attached, I noticed that, in your Weekly sheet, you merged cell I20 and J20 together which should not be so.
In addition, SUMIFS first argument is sum_range which must be at least 2 cells that contains numeric values. In your case, your sum_range is only a single cell which you also merged as pointed earlier.
I think you need to re-design your spreadsheet to use the SUMIFS better. Caption below is how single criteria SUMIFS should work
02-16-2020 11:27 PM
@Jsbluemoon82 Your formula in F4 (monthly) works as it should, but it is quite meaningless. All it does is transfer the value from cell I20 (weekly) if the month = February (D2, weekly) and the week number = 2 (H2, weekly)
Since your weekly schedule only keeps track of the current week, your formula for Week1 now returns -zero-, as either of these rules match (it wants to pick-up I20 for month February and week 1).
If you insist to keep the design of the schedule, you might as well transfer the weekly number to the monthly sheet, manually. Isn't that what you did for January? Though, capturing all your daily readings in one table (Date, Output, Reject, Machine, Comment) will allow you to make summaries and graphs from one single source.