COUNTIF Formula

maoutram

New Member
Joined
Nov 6, 2012
Messages
2
Hi

I'm sure this is a very basic formula but I need help.

In a single cell I want to it return a numerical value for every cell that has a particular word included in its cell. I am trying to use the following formula but perhaps I am using the wrong one;

=COUNTIF(B1:B10,"£70")

Column B = Cost of Fee

I want it to calculate the sum of any cell that contains £70 between B1:B10. If there were only two cells between B1:B10 then it would return £140.00. Other cells within the range may contain I another figure so I only want it calculate those with a particular word included the cell.

Help would be greatfully appreciated.

Regards
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
=COUNTIF(B1:B10,"£70") * 70

would work.
But if you want to expand and start searching for values other than 70 then this calculation will not work.

Some example data would be beneficial.
 
Upvote 0
Hi,

I'm not sure your explanation is completely clear, but I think you need the SUMIF function instead, something like this:
Excel Workbook
BC
170140
210
310
410
510
610
710
810
970
1010
Sheet1
Excel 2010
Cell Formulas
RangeFormula
C1=SUMIF(B1:B10,70)
Note that you probably don't need the '£' sign in the critera assmuing the column B data is numbers formatted as currency. For more information and examples see:
 
Upvote 0
Hi

I'm sure this is a very basic formula but I need help.

In a single cell I want to it return a numerical value for every cell that has a particular word included in its cell. I am trying to use the following formula but perhaps I am using the wrong one;

=COUNTIF(B1:B10,"£70")

Column B = Cost of Fee

I want it to calculate the sum of any cell that contains £70 between B1:B10. If there were only two cells between B1:B10 then it would return £140.00. Other cells within the range may contain I another figure so I only want it calculate those with a particular word included the cell.

Help would be greatfully appreciated.

Regards
Not sure...

Maybe one of these...

=COUNTIF(B1:B10,"£70")*70

=SUMIF(B1:B10,"£70")
 
Upvote 0
Hi

Sorry if I was unclear about what I wanted but both of the formulas provided have worked.



I wanted it to calculate a numerical value but only for cells that had a particular word included in the cell within a certain range. In that case I wanted it to add up cells that £70. Other cells would contain another figure.

Like the COUNTIF formula that well tell you how many cells include the word 'Apple', only I am replacing the word 'Apple' with £70 and then I wanted it to add up all the £70 within a certain range.

Thanks for your help

Much appreciated.
 
Upvote 0
Hi

Sorry if I was unclear about what I wanted but both of the formulas provided have worked.



I wanted it to calculate a numerical value but only for cells that had a particular word included in the cell within a certain range. In that case I wanted it to add up cells that £70. Other cells would contain another figure.

Like the COUNTIF formula that well tell you how many cells include the word 'Apple', only I am replacing the word 'Apple' with £70 and then I wanted it to add up all the £70 within a certain range.

Thanks for your help

Much appreciated.
Good deal. Thanks for the feedback! :cool:
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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