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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,222
Messages
5,594,902
Members
413,950
Latest member
solve22

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