SOLVED

SUMIF values in date range not working

%3CLINGO-SUB%20id%3D%22lingo-sub-391145%22%20slang%3D%22en-US%22%3ESUMIF%20values%20in%20date%20range%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391145%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%20I've%20gotten%20this%20to%20work%20before%20but%20it's%20not%20now.%3C%2FP%3E%3CP%3EI'm%20trying%20to%20total%20the%20commissions%20for%20any%20sales%20made%20within%20a%20date%20range.%20For%20some%20reason%2C%20though%20I've%20entered%20the%20formula%20exactly%20how%20I%20believe%20it's%20worked%20before%2C%20isn't%20working.%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%20in%20the%20last%20sheet%20in%20the%20attached%20file%2C%20M7%20is%20supposed%20to%20sum%20all%20amounts%20from%20Column%20D%20for%20any%20sales%20made%20between%20dates%20H7%20and%20I7%20(%24500%20total).%20Instead%20it's%20coming%20back%20with%20a%20totally%20wrong%20number%2C%20and%20I%20have%20no%20idea%20where%20it's%20pulling%20that%20total%20from.%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-391145%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-392182%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIF%20values%20in%20date%20range%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-392182%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F310795%22%20target%3D%22_blank%22%3E%40DSGN1%3C%2FA%3E%20%2C%20there%20are%20two%20ways%2C%20both%20are%20explained%20here%26nbsp%3B%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3E%3CA%20href%3D%22https%3A%2F%2Fwww.myonlinetraininghub.com%2Fexcel-3d-sumif-across-multiple-worksheets%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.myonlinetraininghub.com%2Fexcel-3d-sumif-across-multiple-worksheets%3C%2FA%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-391162%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIF%20values%20in%20date%20range%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391162%22%20slang%3D%22en-US%22%3EWhat%20would%20be%20the%20formula%20if%20I%20wanted%20it%20to%20sum%20that%20date%20range%203D%2C%20across%20all%20sheets%20in%20the%20workbook%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-391153%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIF%20values%20in%20date%20range%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391153%22%20slang%3D%22en-US%22%3EThanks!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-391149%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIF%20values%20in%20date%20range%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391149%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F310795%22%20target%3D%22_blank%22%3E%40DSGN1%3C%2FA%3E%20%2C%3C%2FP%3E%0A%3CPRE%3E%3DSUMIFS(D4%3AD60%2CB4%3AB60%2C%22%26gt%3B%3D%22%26amp%3BH7%2C%3CFONT%20color%3D%22%23ff0000%22%3E%3CSTRONG%3ED4%3AD60%3C%2FSTRONG%3E%3C%2FFONT%3E%2C%22%26lt%3B%3D%22%26amp%3BI7)%3C%2FPRE%3E%0A%3CP%3Eit%20shall%20be%20B%20column%20for%20the%20second%20criteria%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi there, I've gotten this to work before but it's not now.

I'm trying to total the commissions for any sales made within a date range. For some reason, though I've entered the formula exactly how I believe it's worked before, isn't working. 

For example in the last sheet in the attached file, M7 is supposed to sum all amounts from Column D for any sales made between dates H7 and I7 ($500 total). Instead it's coming back with a totally wrong number, and I have no idea where it's pulling that total from. 

Please help.

4 Replies
Highlighted
Solution

@DSGN1 ,

=SUMIFS(D4:D60,B4:B60,">="&H7,D4:D60,"<="&I7)

it shall be B column for the second criteria

Highlighted
Thanks!
Highlighted
What would be the formula if I wanted it to sum that date range 3D, across all sheets in the workbook?
Highlighted