# summing ranges

#### d0wnt0wn

##### Well-known Member
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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### Scott Huish

##### MrExcel MVP
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
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"

##### MrExcel MVP
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
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

Replies
2
Views
156
Replies
7
Views
599
Replies
9
Views
711
Replies
4
Views
765
Replies
0
Views
235

1,181,609
Messages
5,930,898
Members
436,765
Latest member
kan01

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