Forum Discussion
Solved
Excel
Hi there.... In have two columns , bike_type and customer_type. I want to know how many casual customers have used electric_bike , casual_bike and docked_bike and the same with member customers. I tried few formulas like countif, countifs but didn't find the solution. Can you please help me to know here how to write formula for the same. Thanks.
- There are multiple options including using COUNTIF or COUNTIFS. First are you looking for the sum of casual customers that have used electric, casual, or docked or you want 3 different results?
That said you can get these total by creating a pivot table. If you don't know about or how to use pivot tables, this is a great opportunity to learn.
As for countif you should be able to use COUNTIFS(G1:G100, "casual", B1:B100, "electric_bike") to get a total that are both casual and electric_bike then add the other 2 options if you want the sum:
=COUNTIFS(G1:G100, "casual", B1:B100, "electric_bike")+COUNTIFS(G1:G100, "casual", B1:B100, "casual_bike")+COUNTIFS(G1:G100, "casual", B1:B100, "docked_bike")
alternatively you could use SUMPRODUCT:
=SUMPRODUCT( (G1:G100="casual")*( (B1:B100= "electric_bike")+(B1:B100= "casual_bike")+(B1:B100= "docked_bike") ) )
and if that is an actual TABLE you can use the column NAMEs instead of those ranges so:
=COUNTIFS(Table1[customer_type], "casual", Table1[bike_type], "electric_bike")+ COUNTIFS(Table1[customer_type], "casual", Table1[bike_type], "casual_bike")+ COUNTIFS(Table1[customer_type], "casual", Table1[bike_type], "docked_bike")
not only is that more readable, you also don't have to worry about adjusting the range if you add or delete data
2 Replies
- There are multiple options including using COUNTIF or COUNTIFS. First are you looking for the sum of casual customers that have used electric, casual, or docked or you want 3 different results?
That said you can get these total by creating a pivot table. If you don't know about or how to use pivot tables, this is a great opportunity to learn.
As for countif you should be able to use COUNTIFS(G1:G100, "casual", B1:B100, "electric_bike") to get a total that are both casual and electric_bike then add the other 2 options if you want the sum:
=COUNTIFS(G1:G100, "casual", B1:B100, "electric_bike")+COUNTIFS(G1:G100, "casual", B1:B100, "casual_bike")+COUNTIFS(G1:G100, "casual", B1:B100, "docked_bike")
alternatively you could use SUMPRODUCT:
=SUMPRODUCT( (G1:G100="casual")*( (B1:B100= "electric_bike")+(B1:B100= "casual_bike")+(B1:B100= "docked_bike") ) )
and if that is an actual TABLE you can use the column NAMEs instead of those ranges so:
=COUNTIFS(Table1[customer_type], "casual", Table1[bike_type], "electric_bike")+ COUNTIFS(Table1[customer_type], "casual", Table1[bike_type], "casual_bike")+ COUNTIFS(Table1[customer_type], "casual", Table1[bike_type], "docked_bike")
not only is that more readable, you also don't have to worry about adjusting the range if you add or delete data- Thank you for the valuable inputs. All worked.
