SOLVED

# Excel

Copper Contributor

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

2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Excel

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")

# Re: Excel

Thank you for the valuable inputs. All worked.
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Excel

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")