| A | B | C | D | E |
|---|---|---|---|---|
| Rate | Weight | Count | Item | Category |
| 20$ | 65 | Caps | Sold | |
| 80 | 400 | Caps | Sold | |
| 500 | Caps | Sold | ||
| 90 | 991 | Caps | Sold |
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
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().