Question Need Excel Help

[PsychoPsyops]

Hello all,

I have an excel sheet with a list of employees and what shirts they want, columns include size, color, gender and quantity.
The main problem with this is that some cells under these columns may have multiple items (ex: one Color cell has Black, Blue).
How can I make it so that it counts the items of each separate combination, to make it easy when it comes to ordering them?


Thank you.

 

[Ralston18]

Excel has multiple functions that can readily address such requirements.

Easily googled. But different functions can be used to the same end depending on the details of the data and overall spreadsheet design.

Plus there are "Conditionals" that could be used or capitalized on to do selections, sorts, and counts.

However, the starting point is for you to provide more information about the current spreadsheet: Rows, Columns, Data type etc.

If anything take a screenshot of a representative section of the spreadsheet and post the screenshot herein.

Be sure to redact any personally identifialble information.

Also, add some comments about what you have tried or attempted to date. What where the results?

Key is to show your work first.

 

[PsychoPsyops]

Excel has multiple functions that can readily address such requirements.

Easily googled. But different functions can be used to the same end depending on the details of the data and overall spreadsheet design.

Plus there are "Conditionals" that could be used or capitalized on to do selections, sorts, and counts.

However, the starting point is for you to provide more information about the current spreadsheet: Rows, Columns, Data type etc.

If anything take a screenshot of a representative section of the spreadsheet and post the screenshot herein.

Be sure to redact any personally identifialble information.

Also, add some comments about what you have tried or attempted to date. What where the results?

Key is to show your work first.

Example row with column titles:

First Name | Last Name | Size | Color | Gender | Qty |
_______________________________________________________________
John | Doe | XL | Blue, Red, Black | Mens | 3 |

I tried to make a pivot table with this data, excluding the employee names.
However, the results were not what we wanted, because of the color cells containing more than one option.

I am thinking I need to just create a second table with the quantity of each separate size, each separate color and each separate gender, and then make a pivot table out of that, which I am working on doing now. However, there's probably something I can do to automate this more, I just cannot find it, if it exists.

 

[ex_bubblehead]

To make things work properly cells must contain only a single value. You're going to need multiple lines where there are multiple entries. You pretty things up when you create the report.

 

[PsychoPsyops]

To make things work properly cells must contain only a single value. You're going to need multiple lines where there are multiple entries. You pretty things up when you create the report.

I added multiple lines in the cells that contain multiple entries and put each entry on a separate line, but =countif is still not reading the separate entries.

 

[ex_bubblehead]

This is where you show your work. What does your sheet look like?

COUNTIF function - Microsoft Support

How to use the COUNTIF function in Excel to count the number of cells that meet values you set.

support.microsoft.com

 

[PsychoPsyops]

Never mind, I found that I have to use asterisks within the criteria, rather than line breaks.
I should be all set now.

Thank you.

 

[USAFRet]