Forum Discussion
Solved
Sumifs function
Using sumifs function I am trying to take the sum based on 3 criteria which worked perfectly. I also have a condition such that out of the 3 criteria in some cases, I may have only 2 criteria to tak...
What do you see in the attached version?
I have attached the sample excel file in the link.
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.
- Sorry...
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?
