Averaging a list with specific title

[Karroog]

Hey all, I thank all of you who try and answer my question.

So anyway, I have a formula in a "tracking" sheet that counts how many of a specific word shows up in a "record" sheet. The formula is basically this:

=COUNTIF(tracking!$D$4:$D$69,B8)

The count is basically specific names of vendors for my company. It looks up the name of what its suppose to find (B8) and looks through D4 through D69 to find that specific name.

I have another column H4 through H69 within the tracking sheet. These are the number of days the specific vendor took to finish. I am trying figure out how to average the days where it matches to the vendor name. So if I was looking for a Vendor named "Gary" and that showed up in D6, the formula would then go to H6 and grab the number and plop it into an AVERAGE formula.

I'm not sure how to do this, any help would be great.

Thanks!

[Paul]

Since an average is typically used with more than one number, are you saying that the entry "Gary" would appear multiple times in D4:D69, and you want to average the corresponding values from H4:H69 where D4:D69="Gary"?

If so, the array formula:

=AVERAGE(IF(D4:D69=B8,H4:H69))

After typing this you must press CTRL+SHIFT+ENTER otherwise it will not work.

This assumes your lookup value is in cell B8.

[Karroog]

Since an average is typically used with more than one number, are you saying that the entry "Gary" would appear multiple times in D4:D69, and you want to average the corresponding values from H4:H69 where D4:D69="Gary"?

If so, the array formula:

=AVERAGE(IF(D4:D69=B8,H4:H69))

After typing this you must press CTRL+SHIFT+ENTER otherwise it will not work.

This assumes your lookup value is in cell B8.

Yes, Gary appears 42 times within D4:D69, and whenever Gary appears in that list the formula would grab the number that is in the H column.

I tried the formula, I edited it a little, not sure if I screwed it up since I'm still looking up the numbers in the tracking sheet, would this be it?

=AVERAGE(IF(tracking!$D$4:$D$69=B8,tracking!$H$4:$H$69))

Since I'm still trying to pull the numbers from the Tracking sheet. Gives me a wierd number, did I screw something up?

Sorry forgot the "!", still doesnt work :/

[Paul]

Hi again,

I used the formula on a test sheet of mine and it worked just fine. The only thing I can think of is that you're not pressing CTRL+SHIFT+ENTER after typing it in. You MUST do this since it's an array formula, otherwise it won't calculate properly.

Since you already have the formula in the cell, go to the cell and press F2, then press CTRL+SHIFT+ENTER. You will see braces { and } automatically appear around the formula in your formula bar. (You don't add the braces, Excel does.)

[Karroog]

Is there a different way of doing it on a Mac? Or would it still be Ctrl-Shift-enter? Just want to make sure it would on both systems, Mac and a PC.

[Paul]

I believe with a Mac you would use COMMAND+RETURN. (I've seen it described as the "Daisy key" + RETURN)

I don't have a Mac, so I'm not 100% certain.

[Karroog]

Tried it and it gave me a ridiculous percent. Not sure why this is happening.

=AVERAGE(IF(tracking!D4:D69=B8,tracking!H4:H69))

Thats what I have as the formula. I don't see any errors within it, anyone got any ideas? When I do Command+Return it gives me 3321%, not sure why.

[Karroog]

Tried it and it gave me a ridiculous percent. Not sure why this is happening.

=AVERAGE(IF(tracking!D4:D69=B8,tracking!H4:H69))

Thats what I have as the formula. I don't see any errors within it, anyone got any ideas? When I do Command+Return it gives me 3321%, not sure why.

Nevermind, it worked. The average it seems was 33.21 and it was just giving me a percent. Silly thing I guess. Thanks again, this was a big help!