This is my Data in sheet, (actually there is a lot, its just a sample)

Now i want to see, How many empty cells with pending rates are there where Category and Item is same, but Count or Weight cell is not empty and surely there are 3 cells in Rate Column which are not filled yet.

Formula i am trying in F1 is

=SUMPRODUCT(--(E2:E6="Sold"),--(D2:D6="Caps"),--(A2:A6=""),--(C2:C6<>"") --(B2:B6<>""))

and its returning me the Ans 4 which is wrong, Ans must be 3 Please guide me how to solve the issue

CodePudding user response：

You can try:

=SUM((A2:A5="")*(((B2:B5<>"") (C2:C5<>""))>0)*(D2:D5="Caps")*(E2:E5="Sold"))

Where (((B2:B5<>"") (C2:C5<>""))>0) means we want to check if either column B or column C has any value. Also, SUMPRODUCT() is a remnant from previous versions of Excel to cancel auto-implicit-intersection. In ms365 this is no longer needed due to dynamic arrays, hence you can just use SUM().

CodePudding user response：

or may be if you are using Office 365

=SUMPRODUCT((E1:E5="Sold")*(D1:D5="Caps")*(A1:A5="")*(B1:B5<>"")) (@C1:C5<>"")

or may be

=COUNTIFS(E2:E5,"Sold",D2:D5,"Caps",C2:C5,"<>")