SOLVED

Excel

Copper Contributor

PXL_20230815_011823613.jpg

 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. 

2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution
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.
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution
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

View solution in original post