COUNTIF() and weighted average

merlin_the_magician

Active Member
Joined
Jul 31, 2002
Messages
480
ABCDEFGHIJ
1colorpointscolorpointscolorpointscolorpointscolorpoints
2blue10green20blue10red30blue10
Points blue:

in the simpe example sheet above, I have a table with values. I need to determine the count of each color. Eventually, this count is used in a function calculating weighted average, affecting the score points.

For example blue: i need the count number of the entry "blue" in row 2 (3). In a weighted average, points for "blue" should result in (10/3=3.33)

So......... i figured to make a named range "COLOR" (A2,C2,E2,G,I2) using formula COUNTIF(COLOR,A2) however, returns #VALUE

COUNTIF(A2:J2,A2) does the trick, but... only half of it. That only works outside the table. When i use that the calculate weighted averge directly into cell A2, COUNTIF(A2:J2,A2) ends up in a circular reference.

It apppears as if the named range should be a solid, cojoined range, rather than a collection of cells.
Does anybody have a keen idea on how to fix this?
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hi

It's maybe your example, but I'm not following why the points for blue should be 10.
You can get the count of 3 blues with this =COUNTIFS(B3:K3,"color",B4:K4,"blue")

But why is the total of blue not 30? Giving you 30/3 = 10

1583877777535.png
 
Upvote 0
When i use that the calculate weighted averge directly into cell A2, COUNTIF(A2:J2,A2) ends up in a circular reference.

I don't understand this part of your description. Cell A2 contains the text "blue", so it cannot also hold the weighted average.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

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
Back
Top