Home

Sumif help...

%3CLINGO-SUB%20id%3D%22lingo-sub-837775%22%20slang%3D%22en-US%22%3ESumif%20help...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-837775%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20help%20with%20a%20sumif%2Fsumifs%20formula...%20My%20formula%20currently%20looks%20like%20this%3A%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMIFS(AX3%3AAX433%2CBK3%3ABK433%2C%22%26gt%3B%22%26amp%3B%24CN%243%2CBK3%3ABK433%2C%22%26lt%3B%22%26amp%3B%24CN%244%2CBV3%3ABV433%2C%22%26gt%3B%22%26amp%3B%24CN%243%2CBV3%3ABV433%2C%22%26lt%3B%22%26amp%3B%24CN%244)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20formula%20sums%20AX3%3AAX433%20if%20both%26nbsp%3BBK3%3ABK433%20and%26nbsp%3BBV3%3ABV433%20are%20between%20the%20date%20ranges%20identified%20in%20CN3%20and%20CN4.%20However%2C%20what%20I%20want%20to%20be%20able%20to%20do%20is%20sum%26nbsp%3BAX3%3AAX433%20if%20one%20or%20both%20of%20BK3%3ABK433%20and%26nbsp%3BBV3%3ABV43%20are%20between%20the%20date%26nbsp%3Branges%20identified%20in%20CN3%20and%20CN4.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20don't%20want%20to%20double%20count%20anything%20from%26nbsp%3BAX3%3AAX433%20if%20both%26nbsp%3BBK3%3ABK433%20and%26nbsp%3BBV3%3ABV43%20are%20between%20the%20date%26nbsp%3Branges%20identified%20in%20CN3%20and%20CN4.%20It%20currently%20doesn't%20do%20that%2C%20but%20I%20just%20thought%20I%20should%20mention%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20greatly%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-837775%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESUMIFS%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-837951%22%20slang%3D%22en-US%22%3ERe%3A%20Sumif%20help...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-837951%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403136%22%20target%3D%22_blank%22%3E%40bvjsdfkv1690%3C%2FA%3E%26nbsp%3B%20I%20don't%20think%20you%20can%20do%20that%20with%20a%20Sumifs%2C%20but%20it's%20possible%20with%20a%20Sumproduct%2C%20so%20there%20will%20be%20no%20duplicate%20sums.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMPRODUCT(%3CSPAN%3EAX3%3AAX433%3C%2FSPAN%3E%2C--(((%3CSPAN%3EBK3%3ABK433%3C%2FSPAN%3E%26gt%3B%3CSPAN%3E%24CN%243%3C%2FSPAN%3E)*(%3CSPAN%3EBK3%3ABK433%3C%2FSPAN%3E%26lt%3B%3CSPAN%3E%24CN%244%3C%2FSPAN%3E))%2B((%3CSPAN%3EBV3%3ABV433%3C%2FSPAN%3E%26gt%3B%3CSPAN%3E%24CN%243%3C%2FSPAN%3E)*(%3CSPAN%3EBV3%3ABV433%3C%2FSPAN%3E%26lt%3B%3CSPAN%3E%24CN%244%3C%2FSPAN%3E))%26gt%3B0))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-837953%22%20slang%3D%22en-US%22%3ERe%3A%20Sumif%20help...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-837953%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403136%22%20target%3D%22_blank%22%3E%40bvjsdfkv1690%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20try%20this%20%3CSTRONG%3EArray%20Formula%3C%2FSTRONG%3Ewhich%20requires%20confirmation%20with%20%3CSTRONG%3ECtrl%2BShift%2BEnter%3C%2FSTRONG%3Einstead%20of%20Enter%20alone.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DSUM(--(FREQUENCY(IF(AX3%3AAX433%26lt%3B%26gt%3B%22%22%2CIF(OR(BK3%3ABK433%26gt%3BCN3%2CBV3%3ABV433%3CCN4%3E0))%3C%2FCN4%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERemember%20to%20confirm%20it%20with%20%3CSTRONG%3ECtrl%2BShift%2BEnter%3C%2FSTRONG%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-837954%22%20slang%3D%22en-US%22%3ERe%3A%20Sumif%20help...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-837954%22%20slang%3D%22en-US%22%3EPerfect%2C%20thanks%20for%20your%20help%3C%2FLINGO-BODY%3E
bvjsdfkv1690
New Contributor

Hi,

 

I need help with a sumif/sumifs formula... My formula currently looks like this:  

=SUMIFS(AX3:AX433,BK3:BK433,">"&$CN$3,BK3:BK433,"<"&$CN$4,BV3:BV433,">"&$CN$3,BV3:BV433,"<"&$CN$4)

 

This formula sums AX3:AX433 if both BK3:BK433 and BV3:BV433 are between the date ranges identified in CN3 and CN4. However, what I want to be able to do is sum AX3:AX433 if one or both of BK3:BK433 and BV3:BV43 are between the date ranges identified in CN3 and CN4. 

 

I also don't want to double count anything from AX3:AX433 if both BK3:BK433 and BV3:BV43 are between the date ranges identified in CN3 and CN4. It currently doesn't do that, but I just thought I should mention it.

 

Any help would be greatly appreciated!

 

Thanks

3 Replies

@bvjsdfkv1690  I don't think you can do that with a Sumifs, but it's possible with a Sumproduct, so there will be no duplicate sums.

 

=SUMPRODUCT(AX3:AX433,--(((BK3:BK433>$CN$3)*(BK3:BK433<$CN$4))+((BV3:BV433>$CN$3)*(BV3:BV433<$CN$4))>0))

@bvjsdfkv1690 

 

You may try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

 

=SUM(--(FREQUENCY(IF(AX3:AX433<>"",IF(OR(BK3:BK433>CN3,BV3:BV433<CN4),MATCH(""&AX3:AX433,""&AX3:AX433,0))),ROW($AX$3:$AX$433)-ROW($AX$3)+1)>0))

 

Remember to confirm it with Ctrl+Shift+Enter.

Perfect, thanks for your help
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
217 Replies