• 470K Members
• 9,059 Online
• 568K Conversations

New Contributor

# Sumif help...

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

# Re: Sumif help...

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

# Re: Sumif help...

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.

# Re: Sumif help...

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies