Pivot Table Functions without the Pivot Table

CW-NEN

Board Regular
Joined
Jul 18, 2007
Messages
55
This morning has been interesting. I was given a worksheet with 40K rows. I need to count the number of times a business appears with a cost greater than 0, among other things.

Normally I would use a Pivot Table. Unfortunately, I have to many unique entires for the table to handle.

I was able to count the number of times each business appeared by using the COUNTIF function. Now I need an AVERAGEIF function (which, to my knowledge, does not exist) but it also needs to return the costs similar to how a VLOOKUP works.

Ideally I would have a worksheet that looked like this:

Name | Cost | Count | # of locations with cost > 0 | Average Cost

Currently I have the names and the costs. I derived the count by using a COUNTIF, now I need the last two.

I am stumped.

Thanks for the help
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,025
Office Version
  1. 365
  2. 2016
Hi CW-NEN

Perhaps SUMPRODUCT might be the very beast you are looking for

If you have something like

=SUMPRODUCT(--($A$2:$A$65536=BusinessName),--($B$2:$B$65536=NextCriteria), more arrays go here)

Keep the dimensions the same too.

Hope this helps


Davae
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,982
Messages
5,834,678
Members
430,309
Latest member
Deepjuillet

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