Notifications
Clear all
General Excel Questions & Answers
2
Posts
2
Users
0
Reactions
98
Views
Topic starter
| mode | party | item | qty | books | textbooks | depots |
| Cash | Depot1 | Book100pg | 100 | Book100pg | TextBookB | Depot1 |
| Cash | Depot1 | Book200pg | 25 | Book200pg | TextBookC | Depot2 |
| Cash | Depot3 | Book200pg | 65 | Book400pg | TextBookE | Depot3 |
| Cash | SHOP | PencilLight | 150 | Book800pg | ||
| Chq | SHOP | Book200pg | 150 | |||
| Chq | SHOP | TextBookE | 75 | |||
| Cash | SHOP | TextBookE | 75 | |||
| Chq | SHOP | PencilLight | 35 | |||
| Chq | SHOP | TextBookE | 144 |
SUMPRODUCT(SUMIFS(qty,mode,"Cash",item,books)) the answer is 190
but is one more criteria is added to this ---
SUMPRODUCT(SUMIFS(qty,mode,"Cash",item,books,party,depots)) the answer is 100 which is incorrect
Posted : 24/08/2017 1:20 am
Hello,
I found your issue very interesting so I have played around a bit. See the attached sheet to see my attempt to explain why you get 100 as the result. It is correct value as per Excel logic if you use SUMPRODUCT function. As you see in the sheet I would use DSUM function instead, as you will then get rid of the TRUE or FALSE values + you are trying to use your range as a database. In such scenarios I prefer to use the D* functions.
I don't know if this gives any answer to your issue, but hopefully so.
Br,
Anders
Posted : 24/08/2017 2:08 pm
