Forum Discussion
Sumifs function
What do you see in the attached version?
I'd avoid using entire columns in the following:
=SUM(IF(K1="",1,A2:A100=K1)*IF(K2="",1,B2:B100=K2)*IF(K3="",1,C2:C100=K3)*IF(K4="",1,D2:D100=K4)*E2:E100)
If you don't have Microsoft 365 or Office 2021, use SUMPRODUCT instead of SUM.
when I copy the formula into the shared file I am not getting the right answer. Appreciate if you could guide me.
What do you see in the attached version?
Thanks ... It works perfectly with respect to what I was looking for.
The difference which I see in the attached file is the formula is enclosed with a set of curly brackets (highlighted in red) . {=SUM(IF(K1="",1,A2:A100=K1)*IF(K2="",1,B2:B100=K2)*IF(K3="",1,C2:C100=K3)*IF(K4="",1,D2:D100=K4)*E2:E100)}
Further, I noticed that when the curly brackets are removed the formula doesn't give the right value.
Could you please help me understand what it does?
The formula is an array formula. I use Excel in Microsoft 365; all formulas are automatically array formulas there. (In Office 2021 as well)
In older versions, you have to confirm a formula by pressing Ctrl+Shift+Enter to turn it into an array formula. This adds { } in the formula bar - do not type them yourself.
