AVERAGEIFS across multiple worksheets

[Clooney003]

This is my formula =AVERAGEIFS(January:July!A3:A100,BF3,January:July!G3:G100)

I have 7 seperate worksheets Jan-Jul which every sheet is identical in column setup. I need the formula to find the value of "BF3" in every sheet from A3 down to A100 but i need the average value which is in G3 down to G100. I want the formula to match the row with the value from "BF3" in the A column to match up the value in the G column and give me G's average.

Thanks

[shukla.ankur281190]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[FlameRetired]

Clooney003 AVERAGEIFS isn't a function available in Excel 2003. Please take the time to update your profile. Members propose solutions with that in mind.

[Clooney003]

Here is the file, I hope this works. Also updated profile to match.

[shukla.ankur281190]

Try

BG3

Please Login or Register  to view this content.

Drag down format as percentage

[Clooney003]

Getting a #REF error

Must be the BK reference since I have nothing in column BK

[shukla.ankur281190]

Sorry, put months names ( January, February etc) in sheet 2017 BK3:BK14

Check the attached file.

[Clooney003]

hey yeh that worked.

My question though, when i ran my manual =AVERAGE(January!G4,February!G3,March!G3,April!G3,June!G3,July!G3) BG3 showed 103% but after your formula it reads 106%. Am I misunderstanding the AVERAGE formula?

[Glenn Kennedy]

Is this your problem:

=AVERAGE(January!G4,February!G3,March!G3,April!G3,June!G3,July!G3) BG3

[shukla.ankur281190]

Row\Col	A	B	C
1	Pro %	Id	Month
2	100%	LBRPLAYT	Jan
3	97%	LBRPLAYT	Feb
4	107%	LBRPLAYT	Mar
5	105%	LBRPLAYT	Apr
6	126%	LBRPLAYT	May
7	107%	LBRPLAYT	Jun
8	102%	LBRPLAYT	Jul
9	106%	LBRPLAYT	AVERAGE

[Glenn Kennedy]

I alsoe get 106 with this formula, a slight variant on shukla's:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$BK$3:$BK$14&"'!A1:A500"),BF3,INDIRECT("'"&$BK$3:$BK$14&"'!G1:G500")))/SUMPRODUCT(COUNTIF(INDIRECT("'"&$BK$3:$BK$14&"'!A1:A500"),BF3))

[Clooney003]

there was an ID above LBRPLAYT which switched it to G4. I just dont understand why AVERAGE those individual cells it came out to 103%

[shukla.ankur281190]

You didn't consider May month in your formula if you use below formula then it would return 106%

Please Login or Register  to view this content.

[Clooney003]

WOW, why didnt I see that!! Thats what I get for trying to work Excel at 4am

thank you very much

[shukla.ankur281190]

Glad it helps you.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.