Countifs function for getting count based on two conditions

kay1600

New Member
Joined
Jan 13, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
ClientAHondaVolvoTesla
ClientBMercedesTeslaVolvo
ClientBVolvoHondaMercedes
ClientBTeslaVolvoHonda
ClientAVolvoTeslaMercedes
ClientAVolvoMercedesTesla

Table B:
ClientHondaMercedes
ClientA1
2​
ClientB2
2​

Here is the snapshot:

1642189175111.png
 

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
Joined
Jun 12, 2014
Messages
67,751
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=SUMPRODUCT(($A$3:$A$8=$A11)*($B$3:$B$8=B$10))
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,022
Office Version
  1. 2016
Platform
  1. Windows
Hi,

How about
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
Joined
Jun 12, 2014
Messages
67,751
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 13, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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?

1642196435131.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,751
Office Version
  1. 365
Platform
  1. Windows
You are going to struggle to do that with a formula, for that number of rows & columns
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,022
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jan 13, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 18, 2015
Messages
11,812
I set up a worksheet comparable in size, and used FILTER.

Book1 (version 2).xlsb
ABCDEFG
1HondaMercedes
2Client72339444410
3Client8046780
4
5
6Client7HondaVolVoTeslaHondaVolVoTesla
7Client8MercedesTeslaVolvoMercedesTeslaVolvo
8Client9VolvoHondaMercedesVolvoHondaMercedes
9Client10TeslaVolVoHondaTeslaVolVoHonda
10Client11VolvoTeslaMercedesVolvoTeslaMercedes
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
Joined
Jan 5, 2022
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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
 
Learn Excel from Bill Jelen

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

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top