Pivot Table Functions without the Pivot Table

[CW-NEN]

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

 

[dave3009]

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