Sumifs

%3CLINGO-SUB%20id%3D%22lingo-sub-1176559%22%20slang%3D%22en-US%22%3ESumifs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1176559%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20use%20SUMIFS%20to%20register%20weekly%20totals%20from%20one%20worksheet%20to%20another...%3C%2FP%3E%3CP%3EFor%20example%2C%20I%20have%20one%20worksheet%20that%20I%20manually%20enter%20data%20on%20week%20by%20week%20and%20i%20would%20like%20to%20automatically%20save%20the%20data%20on%20another%20sheet%20in%20categories%20ranging%20from%20week%201%2C%20week%202%20etc...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eso...%20I%20have%20managed%20thus%20far%20to%20save%20the%20data%20for%20week%201%20(for%20example)%20using%20the%20formula%26nbsp%3B%3DSUMIFS(Weekly!I20%2CWeekly!D2%2C%22february%22%2CWeekly!H2%2C%221%22).%20This%20works%20fine%2C%20but%20when%20I%20alter%20the%20criteria%20%E2%80%9C%3CSPAN%3EWeekly!H2%2C%221%22%20to%26nbsp%3BWeekly!H2%2C%222%22%2C%20its%20erases%20the%20previous%20total...%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThis%20is%20hard%20to%20explain%20and%20I%20feel%20this%20makes%20no%20sense%20whatsoever%2C%20but%20hopefully%20someone%20out%20there%20can%20see%20through%20the%20madness...%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThanks%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1176559%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1176570%22%20slang%3D%22en-US%22%3ERe%3A%20Sumifs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1176570%22%20slang%3D%22en-US%22%3EHello%2C%20kindly%20attach%20a%20sample%20file%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1176596%22%20slang%3D%22en-US%22%3ERe%3A%20Sumifs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1176596%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421698%22%20target%3D%22_blank%22%3E%40Abiola1%3C%2FA%3E%26nbsp%3BThanks%20for%20getting%20in%20contact%20here%20are%20some%20photos%20that%20may%20help%20describe%20what%20i%20mean...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1176605%22%20slang%3D%22en-US%22%3ERe%3A%20Sumifs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1176605%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F559902%22%20target%3D%22_blank%22%3E%40Jsbluemoon82%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHello%2C%20based%20on%20the%20pictures%20attached%2C%20I%20noticed%20that%2C%20in%20your%26nbsp%3B%3CSTRONG%3EWeekly%26nbsp%3B%3C%2FSTRONG%3Esheet%2C%26nbsp%3Byou%20merged%20cell%20I20%20and%20J20%20together%20which%20should%20not%20be%20so.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20addition%2C%20SUMIFS%20first%20argument%20is%26nbsp%3B%3CSTRONG%3Esum_range%26nbsp%3B%3C%2FSTRONG%3Ewhich%20must%20be%20at%20least%202%20cells%20that%20contains%20numeric%20values.%20In%20your%20case%2C%20your%26nbsp%3B%3CSTRONG%3Esum_range%26nbsp%3B%3C%2FSTRONG%3Eis%20only%20a%20single%20cell%20which%20you%20also%20merged%20as%20pointed%20earlier.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20think%20you%20need%20to%20re-design%20your%20spreadsheet%20to%20use%20the%20SUMIFS%20better.%20Caption%20below%20is%20how%20single%20criteria%20SUMIFS%20should%20work%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Abiola1_0-1581897077391.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F171461i30516863B9D0FB16%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Abiola1_0-1581897077391.png%22%20alt%3D%22Abiola1_0-1581897077391.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1177034%22%20slang%3D%22en-US%22%3ERe%3A%20Sumifs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1177034%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F559902%22%20target%3D%22_blank%22%3E%40Jsbluemoon82%3C%2FA%3E%26nbsp%3BYour%20formula%20in%20F4%20(monthly)%20works%20as%20it%20should%2C%20but%20it%20is%20quite%20meaningless.%20All%20it%20does%20is%20transfer%20the%20value%20from%20cell%20I20%20(weekly)%20if%20the%20month%20%3D%20February%20(D2%2C%20weekly)%20%3CU%3Eand%3C%2FU%3E%20the%20week%20number%20%3D%202%20(H2%2C%20weekly)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESince%20your%20weekly%20schedule%20only%20keeps%20track%20of%20the%20current%20week%2C%20your%20formula%20for%20Week1%20now%20returns%20-zero-%2C%20as%20either%20of%20these%20rules%20match%20(it%20wants%20to%20pick-up%20I20%20for%20month%20February%20and%20week%201).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20insist%20to%20keep%20the%20design%20of%20the%20schedule%2C%20you%20might%20as%20well%20transfer%20the%20weekly%20number%20to%20the%20monthly%20sheet%2C%20manually.%20Isn't%20that%20what%20you%20did%20for%20January%3F%20Though%2C%20capturing%20all%20your%20daily%20readings%20in%20one%20table%20(Date%2C%20Output%2C%20Reject%2C%20Machine%2C%20Comment)%20will%20allow%20you%20to%20make%20summaries%20and%20graphs%20from%20one%20single%20source.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi 

 

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...

 

Thanks

4 Replies
Highlighted
Hello, kindly attach a sample file
Highlighted

@Abiola1 Thanks for getting in contact here are some photos that may help describe what i mean...

Highlighted

@Jsbluemoon82 

 

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

 

Abiola1_0-1581897077391.png

 

Highlighted

@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.

Related Conversations
Sumif help...
bvjsdfkv1690 in Excel on
3 Replies
SumIfs Help
bb94twins in Excel on
10 Replies
Help with Cash-flow schedule
yossivan in Excel on
3 Replies
SUMIF with multiple conditions
Abbie Jorden in Excel on
1 Replies