# Calculate an average using multiple criteria on 2 worksheets

1. ## Calculate an average using multiple criteria on 2 worksheets

Hi,

I am making a spreadsheet to track fuel costs from 3 vendors, and I want to calculate the average price per litre for each vendor, for each month.

I have 2 worksheets: (1) Data Entry, and (2) AVG PPL

Data will be entered in the Data Entry worksheet as follows:

Column A: Vendor name
Column B: Period (i.e.: Sept. 2010)
Column C: The price per litre.

I want to calculate the average price per litre on the "AVG PPL" worksheet based on the data inputted into the "Data Entry" worksheet.

For example: in cell B2 in the "AVG PPL" worksheet I want to calculate the average price per litre for all entries in the "Data Entry" worksheet that match Vendor A, and the period Sept. 2009.

My problem is that I don't know what formula to use in cell B2 to calculate this average. I thought it would look something like this:

=average(if(and('Data Entry'!\$A\$A="Vendor A",'Data Entry'!\$B\$B="Sept 2009")'Data Entry'!\$C\$C))

but this doesn't work.

A copy of the spreadsheet is attached.

Thanks  Register To Reply

2. ## Re: How to calculate an average using multiple criteria on 2 worksheets

How about a quick and easy Pivot table...  Register To Reply

3. ## Re: How to calculate an average using multiple criteria on 2 worksheets

Or this much more complex formula in B2 which can be dragged across and down;
=IF(SUMPRODUCT(--('Data Entry'!\$A\$2:\$A\$1000=\$A2),--('Data Entry'!\$B\$2:\$B\$1000=B\$1))=0,0,SUMPRODUCT(--('Data Entry'!\$A\$2:\$A\$1000=\$A2),--('Data Entry'!\$B\$2:\$B\$1000=B\$1),'Data Entry'!\$C\$2:\$C\$1000)/SUMPRODUCT(--('Data Entry'!\$A\$2:\$A\$1000=\$A2),--('Data Entry'!\$B\$2:\$B\$1000=B\$1)))  Register To Reply

4. ## Re: Calculate an average using multiple criteria on 2 worksheets

It works!

Thanks ChemistB. That is indeed a complex formula.

I'm not familar with pivot tables, but I will search this forum for tutorials. Thanks NBVC.  Register To Reply

5. ## Re: Calculate an average using multiple criteria on 2 worksheets

Hi Guys, I am following this topic and am comparing the results from the pivot table and the formula which are different!  Register To Reply

6. ## Re: Calculate an average using multiple criteria on 2 worksheets

Hi Alm,
Just double checked and the values do match up on the example given. See attached. Pivot Table is definitely the way to go, however.  Register To Reply