Count changes from A to B in a dataset

**apedrog** · 07-07-2019, 04:46 AM

Hello guys.
I need your help with choosing the appropriate Excel formula (common problem for people in this forum...)
Let's say I have the following premises. Ten people often go to a certain restaurant. The date and dish that they had at that restaurant have been registered for the past 3 months. In the beginning there was only one dish in the menu, let's say rice. At some point the restaurant introduced pasta in the menu. Some people stuck with rice while others started eating pasta (these guys did not ever switch back to rice). These people don't go together to the restaurant, therefore there might more entries in the table for some of them than the others. I want to respond to the following question: how many people have switched from rice to pasta, per month and across the entire study? The real dataset I need to analyze to is more complicated than this one, so manual analysis would be too time-consuming. Formulas are required. Can you guys help me out?
Thanks in advance.

**Glenn Kennedy** · 07-07-2019, 05:14 AM

This is wayyy too vague.

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually).

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

**apedrog** · 07-07-2019, 06:23 AM

Here it is.
Thanks.

**Pepe Le Mokko** · 07-07-2019, 06:26 AM

Please update your profile as necessary to reflect your location ( country is OK, no need to be more precise). Regional settings can be important to give a correct answer in some cases.
Thank you

**apedrog** · 07-07-2019, 06:53 AM

Sorry about that. I've updated it.

**avk** · 07-07-2019, 07:24 AM

Firstly change your date column format as English (United States)
With simple countifs function with criteria id, dish, month you can count how many people choose dish.

**apedrog** · 07-07-2019, 08:07 AM

Hey avk. Thanks for your time.
I understand how you counted using COUNTIF. However, what I need is to find out how many IDs have switched from Rice to Pasta. For example, from your table one can visually see that for July the answer is 0. But I would need to find that answer automatically and then apply it to a large dataset. Thank you.

**Glenn Kennedy** · 07-07-2019, 09:15 AM

This is a partial answer (for the whole period). I'll explain as required, but take a look. Is this what you had in mind? I was still a bit in the dark...

**Pepe Le Mokko** · 07-08-2019, 03:34 AM

Originally Posted by apedrog

Sorry about that. I've updated it.

Thanks for the update

**apedrog** · 07-09-2019, 03:55 AM

Thank you for your help. Let me have a look.

Count changes from A to B in a dataset

LinkBack

Thread Tools

Rate This Thread

Display

Count changes from A to B in a dataset

Re: Count changes from A to B in a dataset

Re: Count changes from A to B in a dataset

Re: Count changes from A to B in a dataset

Re: Count changes from A to B in a dataset

Re: Count changes from A to B in a dataset

Re: Count changes from A to B in a dataset

Re: Count changes from A to B in a dataset

Re: Count changes from A to B in a dataset

Re: Count changes from A to B in a dataset

Thread Information

Users Browsing this Thread

Similar Threads

COUNT occurances within a year across columns in a broad dataset

[SOLVED] How to use COUNTIF on a dataset and getting only count from 2 conditions

rearrange database like dataset into table for large dataset better option INDEX MATCH

Reduce the large dataset into smaller dataset using conditions

[SOLVED] Vertical chart dataset needs to be converted into pivotable dataset

Copying a dataset multiple times - and selecting titles for this dataset

Dataset: count and return if user has both R1 and R2

Bookmarks

Bookmarks

Posting Permissions