# Countifs function for getting count based on two conditions

#### kay1600

##### New Member
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:

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=SUMPRODUCT((\$A\$3:\$A\$8=\$A11)*(\$B\$3:\$B\$8=B\$10))``

#### jtakw

##### Well-known Member
Hi,

Excel Formula:
``=SUMPRODUCT((\$A\$3:\$A\$8=\$A11)*(\$B\$3:\$B\$8=B\$10))``

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

Excel Formula:
``=SUMPRODUCT((\$A\$3:\$A\$8=\$A11)*(\$B\$3:\$D\$8=B\$10))``

#### Fluff

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

#### kay1600

##### New Member

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

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

#### jtakw

##### Well-known Member

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

##### New Member
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

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

Book1 (version 2).xlsb
ABCDEFG
1HondaMercedes
2Client72339444410
3Client8046780
4
5
7Client8MercedesTeslaVolvoMercedesTeslaVolvo
8Client9VolvoHondaMercedesVolvoHondaMercedes
9Client10TeslaVolVoHondaTeslaVolVoHonda
11Client12VolvoMercedesTeslaVolvoMercedesTesla
Sheet14
Cell Formulas
RangeFormula
B2:C3B2=SUMPRODUCT(--(FILTER(\$B\$6:\$JIX\$200000,\$A\$6:\$A\$200000=\$A2)=B\$1))
E2E2=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

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

Excel Formula:
``=SUM((\$A\$1:\$A\$203590=\$A203596)*(\$B\$1:\$AWS203590=\$C5))``

I hope that helps!

Regards,
Ken

Replies
13
Views
647
Replies
5
Views
221
Replies
2
Views
84
Replies
4
Views
105
Replies
4
Views
445

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,467
Messages
5,764,498
Members
425,219
Latest member
datdanigg

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back