summing ranges

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
is there a formula to sum a range that may have text in some of the cells....

and in another cell but that same range if the same text occurs 3 times it returns "something"
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Are you wanting to count cells that have certain text in them?

And then you want to indicate if that text occurs in your range exactly three times?

Is this correct?

If not, you may want to explain this in more detail, and perhaps post a sample of your sheet with expected results. Link to Colo's HTML Maker is at the bottom of this page.
 

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
its like this lets say i have a range in a1 to a10

1
2
4
6
7
hat
4
hat
2
4

sum a1:10.....the next time i do this.. there might not be any text or it may be in a different spot


then also if hat is in there 3 times id lke the cell a12 to return lets say "winner"
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
d0wnt0wn said:
its like this lets say i have a range in a1 to a10

1
2
4
6
7
hat
4
hat
2
4

sum a1:10.....the next time i do this.. there might not be any text or it may be in a different spot


then also if hat is in there 3 times id lke the cell a12 to return lets say "winner"

What if the count of "hat" is above 3? Am I understanding it right that you want to see "winner" in A12 if there is 3 or more hats in A1:A10, otherwise a sum of the numbers in A1:A10?
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
Here is your code in A11
=SUM(D11:D20)
Here is your code in A12
=IF(COUNTIF(D11:D20,"hat")=3,"WINNER","Less than two")

Hope this helps,
Michael
 

Forum statistics

Threads
1,147,507
Messages
5,741,566
Members
423,667
Latest member
Kai_357

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