Countifs function using two variables; a specific name and the count of a type of thing

**seanpatrick76** · 06-14-2017, 08:56 PM

Hey all,

I've been bashing my head off this problem for over 2 hours. I can NOT figure out how to return a countifs value based on my required criteria.

For Example, say I have a table with 4 columns, Date, Name of person, Type of car, Number of cars (of that type):
So for example, in my table:

Jan 1, 2017 - Jeff - Buick - 6
Jan 1, 2017 - Jeff - Nissan - 4
Jan 2, 2017 - Jeff - Buick - 1
Jan 3, 2017 - Jeff - Ford - 3
Jan 2, 2017 - Steve - Chevy - 6
Jan 3, 2017 - Steve - Buick - 3

I want to return the number of different types of cars Jeff has Identified.

I know the countifs will return a number based on two criteria, one his name, but how do I get the count of different cars he's spotted (not the total count). So I would need to return a value of 3 in this case, Jeff has seen 3 specific types of cars.

=Sumifs([Tblname][name],"Jeff", where do I go from here?

Thanks so much! So frustrating!

**JeteMc** · 06-14-2017, 10:30 PM

Assuming that the data is in the range A2:D7, an array entered formula* for yielding the number of types of cars seen by Jeff is:

Formula:

Please Login or Register  to view this content.

*Array entered formulas are activated by simultaneously pressing the Ctrl, Shift and Enter keys while the cell is in edit mode.
Let us know if you have any questions.

**FlameRetired** · 06-14-2017, 11:36 PM

Another way similar to JeteMc's. Assuming data is in A2:D7 and names are listed starting in A10 try this array entered formula in B10 and fill down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

Row\Col	A	B	C	D
1	Dates	Names	Makes	Counts
2	1-Jan-17	Jeff	Buick	6
3	1-Jan-17	Jeff	Nissan	4
4	2-Jan-17	Jeff	Buick	1
5	3-Jan-17	Jeff	Ford	3
6	2-Jan-17	Steve	Chevy	6
7	3-Jan-17	Steve	Buick	3
8
9
10	Jeff	3
11	Steve	2

Countifs function using two variables; a specific name and the count of a type of thing

LinkBack

Thread Tools

Rate This Thread

Display

Countifs function using two variables; a specific name and the count of a type of thing

Re: Countifs function using two variables; a specific name and the count of a type of thin

Re: Countifs function using two variables; a specific name and the count of a type of thin

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] SUMPRODUCT IF(OR) type of thing

[SOLVED] Trying to make a database type thing

[SOLVED] Countifs function to count cell color

how to count using countifs function for multiple criteria

[SOLVED] Odd Type Mismatch with variables and the month function.

[SOLVED] format cell to always insert specific thing, when a specific name is typed???

Excel calendar search macro - type thing

Bookmarks

Bookmarks

Posting Permissions