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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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)
 
Upvote 0
Solution
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!
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,623
Latest member
Techenthusiast

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