How to get Countif to work this out?
 

Hi all,

I have as an example:

Columns
A B
apples----------------------2
oranges--------------------3
apples----------------------4
apples----------------------5
oranges--------------------3

How can I get the totals: apples=11 and oranges=6 using Countif

Any help is greatly appreciated,
Emilio

How to get Countif to work this out?
 

On Sep 15, 6:12 pm, Wind54Surfer
wrote:
Hi all,

I have as an example:

Columns
A B
apples----------------------2
oranges--------------------3
apples----------------------4
apples----------------------5
oranges--------------------3

How can I get the totals: apples=11 and oranges=6 using Countif

Any help is greatly appreciated,
Emilio

Use sumif instead of countif:
=SUMIF(A:A;"apples";B:B)

Per Erik

How to get Countif to work this out?
 

Nel ,
Wind54Surfer ha scritto:
Hi all,

I have as an example:

Columns
A B
apples----------------------2
oranges--------------------3
apples----------------------4
apples----------------------5
oranges--------------------3

How can I get the totals: apples=11 and oranges=6 using Countif

Any help is greatly appreciated,
Emilio

Hi Emilio,

instead you should use the SUMIF function, so if yur table is A2:B5, to have
the sum of apples you can use the formula:

=SUMIF(A2:A5;"apples";B2:B5)

or, better, if you use a cell to fix the criteria, for example C2, the
formula will be:

=SUMIF(A2:A5;C2;B2:B5)

in this way if you write in C2 apples, the formula will return 11, if you
write oranges you will have 6.


--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.


Thanks in advance for your feedback.


Ciao


Franz Verga from Italy

How to get Countif to work this out?
 

If you have a large list, not confined to apples and oranges, try the Data
-Filter- Advanced Filter facility. ( If you are new the steps to be taken
will be as follows:

i) Ensure that the data range has got titles, say "Fruit" in one column,
"No," in the next and so on
ii) Keep the cursor on the first cell containing data
iii) Click Data -Filter - Advanced Filter - select the first column entirely
containing the names of fruits ( including the column title - "Fruit")
iv) click on "Copy to another location" button and "Unique Records only" box
v) select the first cell of a new location to copy the unique names of the
fruits ( in this case only "Apples" and "Oranges" will appear below the
column title "Fruit")
vi) Then in the cell next to the first fruit name in the filtered list use
the formula suggested by Franz Verga i.e., =SUMIF(A2:A5;C2;B2:B5) --- here
A2:A5 will represent the range of data containing the names of fruits (
without including the title cell), c2 will represent the cell containing the
fruit name i.e., adjacent to the one where you are entering the formula and
B2:B5 will represent the range of data containing the number of fruits (
excluding the cell holding the title "No.").
vii) Using F4 key, make A2:A5 and B2:B5 as absolute addresses with the $
mark. Enter and copy the formula down the filtered range to get the values
for all types of fruits.

"Franz Verga" wrote:

Nel ,
Wind54Surfer ha scritto:
Hi all,

I have as an example:

Columns
A B
apples----------------------2
oranges--------------------3
apples----------------------4
apples----------------------5
oranges--------------------3

How can I get the totals: apples=11 and oranges=6 using Countif

Any help is greatly appreciated,
Emilio

Hi Emilio,

instead you should use the SUMIF function, so if yur table is A2:B5, to have
the sum of apples you can use the formula:

=SUMIF(A2:A5;"apples";B2:B5)

or, better, if you use a cell to fix the criteria, for example C2, the
formula will be:

=SUMIF(A2:A5;C2;B2:B5)

in this way if you write in C2 apples, the formula will return 11, if you
write oranges you will have 6.


--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.


Thanks in advance for your feedback.


Ciao


Franz Verga from Italy