Sumif help...

[bvjsdfkv1690]

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

[#2]

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

[#3]

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

[#4]

Perfect, thanks for your help