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

chintwin2

New Member
Joined
Jul 13, 2015
Messages
11
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?

1619656743534.png
 

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

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,533
Office Version
  1. 2016
Platform
  1. Windows
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)
 
Solution

chintwin2

New Member
Joined
Jul 13, 2015
Messages
11
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!
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top