Countifs function for getting count based on two conditions

[kay1600]

I have been struggling to solve this issue for days now.

I have raw data with no specific column structure to it. I want to create a column structure to it so that I can count how many times a specific vehicle was requested by a client. I can accomplish this via hlookup; however, I have too many unique columns (vehicle) and excel runs out of memory upon copying the lookup function. I just need to find the count; so wondering if I can use a countifs function to get the count of clients by vehicle (i.e. table B)

Is there a way I can transform the below table A to table B using 'countifs' function? What will be the B11 formula to get the result=1 (i.e. Honda was only requested once by clientA) in the screenshot?

Table A

ClientA	Honda	Volvo	Tesla
ClientB	Mercedes	Tesla	Volvo
ClientB	Volvo	Honda	Mercedes
ClientB	Tesla	Volvo	Honda
ClientA	Volvo	Tesla	Mercedes
ClientA	Volvo	Mercedes	Tesla

Table B:

Client	Honda	Mercedes
ClientA	1	2​
ClientB	2	2​

Here is the snapshot:

 

[Fluff]

How about

=SUMPRODUCT(($A$3:$A$8=$A11)*($B$3:$B$8=B$10))

 

[jtakw]

Hi,

How about

=SUMPRODUCT(($A$3:$A$8=$A11)*($B$3:$B$8=B$10))

Noticed a typo, maybe change $B$8 to $D$8 ?

=SUMPRODUCT(($A$3:$A$8=$A11)*($B$3:$D$8=B$10))

 

[Fluff]

Oops, well spotted. It should indeed be D8.
@kay1600 ignore my formula in post#2 & use the one that jtakw has posted.

 

[kay1600]

Thanks so much. Your solution works for a smaller dataset; however, I'm working with huge volumes of data. I have 1200 columns and 200K rows. So, excel runs out of resources upon executing the "sumproduct" function you guys suggested. Any idea how we could make this solution for a larger dataset?

 

[Fluff]

You are going to struggle to do that with a formula, for that number of rows & columns

 

[jtakw]

I have no cure for the excel resource problem.
But why is your formula only looking across 2 rows?

=SUMPRODUCT(($A$1:$A$203590=$A203596)*($B$1:$AWS2=$C5))

 

[kay1600]

That was just to determine if the formula works or not. My original formula was

=SUMPRODUCT(($A$1:$A$203590=$A203596)*($B$1:$AWS203590=$C5))

 

[Eric W]

I set up a worksheet comparable in size, and used FILTER.

Book1 (version 2).xlsb
	A	B	C	D	E	F	G
1		Honda	Mercedes
2	Client7	2339	44441		0
3	Client8	0	46780
4
5
6	Client7	Honda	VolVo	Tesla	Honda	VolVo	Tesla
7	Client8	Mercedes	Tesla	Volvo	Mercedes	Tesla	Volvo
8	Client9	Volvo	Honda	Mercedes	Volvo	Honda	Mercedes
9	Client10	Tesla	VolVo	Honda	Tesla	VolVo	Honda
10	Client11	Volvo	Tesla	Mercedes	Volvo	Tesla	Mercedes
11	Client12	Volvo	Mercedes	Tesla	Volvo	Mercedes	Tesla
Sheet14

Cell Formulas
Range	Formula
B2:C3	B2	=SUMPRODUCT(--(FILTER($B$6:$JIX$200000,$A$6:$A$200000=$A2)=B$1))
E2	E2	=SUMPRODUCT((A6:A200000=A2)*(B6:JIX200000))

The E2 was the original formula, and I got the Out of Resources error too.

But also note that I set up the client names in column A to only show up about 20 rows each. When I did, it calculated almost instantly. When the client names showed up about 1000 times each, there was a noticeable lag. If you only have a few clients, you may run out of resources again.

 

[KenU]

I don't have the large data set to test on, but perhaps simply using SUM instead of SUMPRODUCT will help.

=SUM(($A$1:$A$203590=$A203596)*($B$1:$AWS203590=$C5))

I hope that helps!

Regards,
Ken