The below is not so simple as you may think of.
Here is example of table. Initial has 80K of records and many columns.
-- removed inline image ---
I would like to sumporduct to count items from table but is not standard easy sumproduct I want.
Basically I want to sumproduct to count 4 conditions but….in data validation I have also All Names/All Years/All Colours/All Price.
If in cell Name I select e.g. "All Names" sumproduct needs to count the rest of conditions selected but except "Name" in the column. E.g. count how many cars are: 'black' in price '20000' and for Year of '6'.
If Some Name is selected along with the rest of 3 conditions it counts all 4 conditions.
Similar if name is selected and Colour too, 2 remaining are set to all, sumproduct counts only for 2 (years and price).
I know that it can be done but with over 10 IFs formulas plus sumproduct…for every possibility/combination between 4 options.
Is there any easier way to do it?e.g.
=sumproduct((if(sumproduct(--(A1:A15))=0,1,"(A1:A15)")*(if(sumproduct(--(B1:B15))=0,1,"(B1:B15)"))*........
The above is in my mind but ifs put it as a text and it doesn't work like this and not sure if I can put 0 and 1 in the formula to count correctly.
I would be very very appreciated to see what excel experts can come up with.
Here is example of table. Initial has 80K of records and many columns.
-- removed inline image ---
I would like to sumporduct to count items from table but is not standard easy sumproduct I want.
Basically I want to sumproduct to count 4 conditions but….in data validation I have also All Names/All Years/All Colours/All Price.
If in cell Name I select e.g. "All Names" sumproduct needs to count the rest of conditions selected but except "Name" in the column. E.g. count how many cars are: 'black' in price '20000' and for Year of '6'.
If Some Name is selected along with the rest of 3 conditions it counts all 4 conditions.
Similar if name is selected and Colour too, 2 remaining are set to all, sumproduct counts only for 2 (years and price).
I know that it can be done but with over 10 IFs formulas plus sumproduct…for every possibility/combination between 4 options.
Is there any easier way to do it?e.g.
=sumproduct((if(sumproduct(--(A1:A15))=0,1,"(A1:A15)")*(if(sumproduct(--(B1:B15))=0,1,"(B1:B15)"))*........
The above is in my mind but ifs put it as a text and it doesn't work like this and not sure if I can put 0 and 1 in the formula to count correctly.
I would be very very appreciated to see what excel experts can come up with.
