Counting rows per individual

[skalynuik]

I have a slight problem:
Max 2 apples
Max 2 Oranges
Max 3 Beans
Susan 4 Bananas
Steve 1 Orange
Steve 2 Peas
Steve 9 Steaks
Steve 3 Bread
Rosy 1 egg
Rosy 2 Lettuce

I want to count number of items per person, ie
Max 3 items
Susan 1 items
Steve 4 items
Rosy 2 items

How would I go about doing this?

Please and Thank-you, you have helped so many and myself thrice.
Steve

 

[Fluff]

Have a look at CountIf https://exceljet.net/excel-functions/excel-countif-function

 

[skalynuik]

Have a look at CountIf https://exceljet.net/excel-functions/excel-countif-function

I was thinking CountIf but was unsure how to proceed;
example column A could contain any 10 character name (to make it easy, and always say 10 characters representing the people)
Column A is repeated for as many lines as needed per person, so if 5 purchases for say ralphnader the A1:A5 would contain RalphNader, so I just want to know at the end of a excel sheet anywhere from 5 lines to 30000 lines how many different items each person bought.
You could also do this for chores in a family, column A would be for each named family member from 1 to 30000 family members, and each person is assigned different jobs from 1 to 25 different jobs each, so at the end I could just look at a chart saying person 1 has 5 jobs, person 2 has 1 job, person 6 has 15 jobs, just as an example.

I cant quite wrap my head around countif that way.

 

[JPARKHURST]

Are you trying to count in the same sheet as you are recordign data?

If you have your data list:

Max 2 apples
Max 2 Oranges
Max 3 Beans
Susan 4 Bananas
Steve 1 Orange
Steve 2 Peas
Steve 9 Steaks
Steve 3 Bread
Rosy 1 egg
Rosy 2 Lettuce

And you have a dashboard:

Name #Items Bought
Max 7
Susan 4
Steve 15
Rosy 2

Question 1: Do you have a known list of people, or do you need to generate that list?
2. You are wanting a 'count' of how many different items they have bought, not a count of how many purchases, correct? (EG, numbers above, not Max - 3, Susan, 1)
3. Is the number of items and the type of item separated into columns?

It sounds like you may want to have a sumif, unless i'm missing something?

if you are doing this in a formula, and assuming the data list exists on sheet called "Data", and display is on sheet called "Dashboard", assuming headers are a1-b1, a2 is name and # items are b2, description b3.

Dashboard (worksheet)

Name         #Items  purchased
Max            =sumif(Data!A:A,$A2,Data!B:B)

You could also do this with a macro, and find your range dynamically. There may be a way to do that with formulas, but I'm still learning myself.

Fluff may correct me, but otherwise HTH,

Jon

 

[skalynuik]

Wow, interesting approach.

Dashboard Sheet1
Data is on Sheet2

Column A is the names, can and will be different each time I would use this, so a list reference is out.
Column C has then number of purchased items (not important for what I need)
Column D has the different item purchased

So in the dashboard I would want for each individual name in column A, I would get a number for the items purchase.
ie:
SamSpade Items bought:3
RalphTime Items bought 5
MiaMita Items bought 1

But names could be different the next time I use the formula, also could be the item name different.
So I just need to know the number of different items each bought, not how many of each.

Also is there a way to do it so I do not have to type each name from column A in the dashboard, because there could be 5 to 10000 rows.(Arbitrary number)
So when done I could click say the SamSpade entry, and I would then see only the SamSpade Items, in the data sheet.

I hope I am explaining this right, even I confuse myself sometimes with excel, for what I want.

Thanx for the help.

 

[JPARKHURST]

OK, so Fluff was correct in the countif, but I think the variable names will give you some issues. Can you do this in a macro? If not, I will do some research. you will essentially need to create an array of names adn then spit those out, then search against that list. Not a big deal, but I'm not sure how to get it in a formula, especially with that many entries.

I'm assuming names will be unique?

 

[skalynuik]

OK, so Fluff was correct in the countif, but I think the variable names will give you some issues. Can you do this in a macro? If not, I will do some research. you will essentially need to create an array of names adn then spit those out, then search against that list. Not a big deal, but I'm not sure how to get it in a formula, especially with that many entries.

I'm assuming names will be unique?

Yes names are unique.

Thank-you

I will read more on countif

 

[JPARKHURST]

Are quantities mixed with descriptions?

A1 B1 C1
Name Qty Description
Max 2 Apples


A1 B1
Max 2 Apples


This is where I ended up. Sorry I couldn't help more tonight, but got swamped.

This formula (for me) is giving me a bum first row (0), and it requires to be copied on every line.

I don't know that I asked, do we ahve to do formula or can you do a macro?

=INDEX(A:A,MATCH(0,COUNTIF($H$4:H4,A:A),0))

 

[skalynuik]

thanx for the help, this should help

 

[JPARKHURST]

Post if you have problems. Like I said, i remember that giving me a blank row then working, but my excel has been a little questionable lately, so just let me know if it is working or not and we can take another look.