Sumifs function

[pillaisg78]

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 take for the sumifs function and one among the 3 will be kept blank. How can it be achieved?

[#2]

@pillaisg78 

Can you provide a more specific example?

[#3]

@HansVogelaar 

I have attached the sample excel file in the link. 

https://1drv.ms/x/s!AvqHVc_GIXwehkO8VheugLQLYMDf?e=hMSa7j 

[#4]

@pillaisg78 

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.

[#5]

Sorry...
when I copy the formula into the shared file I am not getting the right answer. Appreciate if you could guide me.

[#6 · Accepted solution]

@pillaisg78 

What do you see in the attached version?

[#7]

@HansVogelaar 

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?

[#8]

@pillaisg78 

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.