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 custom...
- 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
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
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.
