CountIf & Vlookup Combinatoin

[sc0ttish]

Hello everyone,

I've trying to set up a formula, which i thought would be easy but seem to be stuck.

My spreadsheet has two worksheets House List & Brandcount

House List has the account number in column C and the account number in the Brandcount is in column A.
In the Brandcount worksheet in column B is a list of brands purchased (Brand 1, Brand 2, Brand 3, Brand 4, Brand 5)

What i wanted to do is in House List is in column F count the brands purchased by each account number, is:
Account 12345 , 3 brands

I've tried using a combination of countif and vlookup but not getting far.

Can someone point me in the right direction.
Thanks

 

[EFANYoutube]

Ok so this will work but you have to have the brand your looking for in one of the columns in House list also so I have just put this in column D

=COUNTIFS(Brandcount!A:A,'House List'!C2,Brandcount!B:B,'House List'!D2)

 

[EFANYoutube]

Book1
	A	B	C	D	E	F
1
2			1	Brand2		0
3			2	Brand2		1
4			3	Brand2		0
5			4	Brand2		0
6			5	Brand2		1
7			6	Brand2		0
House List

Cell Formulas
Range	Formula
F2:F7	F2	=COUNTIFS(Brandcount!A:A,'House List'!C2,Brandcount!B:B,'House List'!D2)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[sc0ttish]

Ok so this will work but you have to have the brand your looking for in one of the columns in House list also so I have just put this in column D

=COUNTIFS(Brandcount!A:A,'House List'!C2,Brandcount!B:B,'House List'!D2)

Thanks @EFANYoutube , sorry maybe should have included some more data.
I am looking for the cells in column F to have the total number of brands stocked out of all the brands we can supply.
Not a count of separate brands.

This is an example of my master list.

A	B	C	D	E	F
Plan	Account 1	123456	23/12/2021	Name
Plan	Account 2	134567	04/01/2022	Name
Plan	Account 3	159647	09/01/2022	Name
Plan	Account 4	156491	31/01/2022	Name
Plan	Account 5	136412	15/01/2022	Name

This is an example of the Brandcount sheet

A	B
123456	Brand 1
123456	Brand 1
123456	Brand 3
123456	Brand 1
159647	Brand 1
159647	Brand 2
136412	Brand 3
136412	Brand 3
136412	Brand 3
136412	Brand 3

So my expected return for account 123456 would be 2, as they have bought Brand 1 and Brand 3 (although they have bought Brand 1 three times that doesnt matter).
Account 159647 would also be 2 but Account 136412 would be 1 as they bought Brand 3 four times but its only 1 brand purchased.

Hope this makes sense.
Thanks for the help

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F
1
2	Plan	Account 1	123456	23/12/2021	Name	2
3	Plan	Account 2	134567	04/01/2022	Name	0
4	Plan	Account 3	159647	09/01/2022	Name	2
5	Plan	Account 4	156491	31/01/2022	Name	0
6	Plan	Account 5	136412	15/01/2022	Name	1
7
8
9
10
11	123456	Brand 1
12	123456	Brand 1
13	123456	Brand 3
14	123456	Brand 1
15	159647	Brand 1
16	159647	Brand 2
17	136412	Brand 3
18	136412	Brand 3
19	136412	Brand 3
20	136412	Brand 3
Main

Cell Formulas
Range	Formula
F2:F6	F2	=IFERROR(ROWS(UNIQUE(FILTER($B$11:$B$20,$A$11:$A$20=C2))),0)

 

[sc0ttish]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F
1
2	Plan	Account 1	123456	23/12/2021	Name	2
3	Plan	Account 2	134567	04/01/2022	Name	0
4	Plan	Account 3	159647	09/01/2022	Name	2
5	Plan	Account 4	156491	31/01/2022	Name	0
6	Plan	Account 5	136412	15/01/2022	Name	1
7
8
9
10
11	123456	Brand 1
12	123456	Brand 1
13	123456	Brand 3
14	123456	Brand 1
15	159647	Brand 1
16	159647	Brand 2
17	136412	Brand 3
18	136412	Brand 3
19	136412	Brand 3
20	136412	Brand 3
Main

Cell Formulas
Range	Formula
F2:F6	F2	=IFERROR(ROWS(UNIQUE(FILTER($B$11:$B$20,$A$11:$A$20=C2))),0)

Thanks @Fluff

Formula looks great, but its not giving me the expected results for some reason.
Its giving me a 0 result in all the cells in column F, then where there is no account number in Column C its giving me a 1.

Not sure if i'm being silly, but tried putting the date into a new sheet and changed the formula with the new sheetname but still the same?

 

[Fluff]

In that case check that all your account numbers on both sheets are either text or numbers, but not a mix of both

 

[sc0ttish]

In that case check that all your account numbers on both sheets are either text or numbers, but not a mix of both

@Fluff
Proper school boy error, fantastic.

One last question.
When there is no account number in column C it returns a '1' result. How do i stop this?

 

[Fluff]

How about

=IF(C2="","",IFERROR(ROWS(UNIQUE(FILTER($B$11:$B$200,$A$11:$A$200=C2))),0))