# Function to count values in one column if another column contains text in a single cell

#### chintwin2

##### New Member
Hi all,
Not sure if the title makes any sense, but I have a spreadsheet of names in Column B, with those names showing up in Columns D, F, and H. Column D is 3 points, Column F is 2 points, and Column H is 1 point. So if someone's name shows up 3 times in each of Columns D, F, and H, they should have a total of (9+6+3=18 points). I feel there is probably an easier way to set it up than I have done. Essentially, I have manually input names in a sumif function for each column and sum Columns E, G, and I. See partial screenshot for reference. I'm wondering if there is a way to automatically call up the text in the appropriate B column cells (i.e. call up text in B2 instead of typing "emma") and instead of summing Columns E, G, and I, could I take those out and somehow integrate that into the function? Maybe multiply by appropriate number based on how many times the name appears?

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

##### Well-known Member
Hi Chintwin2,

I'm not sure if I'm following correctly but does this work for you?

Chintwin2.xlsx
BCDEFGHIJK
1Name321Total
2Emmaemmafleuremma12
3Fleursarahemmagemma8
4Gemmafleursarahgemma3
5Joanemmaemmagemma0
6Sarahfleursarahemma7
Sheet1
Cell Formulas
RangeFormula
K2:K6K2=(COUNTIF(\$C\$2:\$C\$22,B2)*D\$1)+(COUNTIF(\$E\$2:\$E\$22,B2)*F\$1)+(COUNTIF(\$G\$2:\$G\$22,B2)*H\$1)

#### chintwin2

##### New Member
Hi Chintwin2,

I'm not sure if I'm following correctly but does this work for you?

Chintwin2.xlsx
BCDEFGHIJK
1Name321Total
2Emmaemmafleuremma12
3Fleursarahemmagemma8
4Gemmafleursarahgemma3
5Joanemmaemmagemma0
6Sarahfleursarahemma7
Sheet1
Cell Formulas
RangeFormula
K2:K6K2=(COUNTIF(\$C\$2:\$C\$22,B2)*D\$1)+(COUNTIF(\$E\$2:\$E\$22,B2)*F\$1)+(COUNTIF(\$G\$2:\$G\$22,B2)*H\$1)
Hmm in principle it seems to work. Most names are working the way it should but getting random returns of 0! Will keep troubleshooting. Thanks!

#### chintwin2

##### New Member
Hmm in principle it seems to work. Most names are working the way it should but getting random returns of 0! Will keep troubleshooting. Thanks!
Figured it out - had a space after the text in the cell

You're welcome!

Replies
2
Views
72
Replies
1
Views
117
Replies
1
Views
97
Replies
8
Views
98
Replies
0
Views
59

1,132,642
Messages
5,654,547
Members
418,139
Latest member
nimesh72

### 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.

### Which adblocker are you using?

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

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