COUNTIF or SUMIF or IF

[sachinattri]

Dear All,

I have this set of data

Name Value Third Column FourthColumn
Count Names
Total Value
A 15 1
15
B 30
B 40 2
70
C 5
C 10 2
15
D 15
D 30
D 5
E 15
E 30

I want in a separate Third Column
a) Count the number of Names
b) get Total values corresponding to those names as show in th
example. I want to get a function that can help me calculate the Thir
and the Fouth column values.

Anything you need to know, please reply I am online only.

Thank you

Sachi

 

[Joey]

Try this

In column C
=COUNTIF(A11:A19,"A")

In Column D
=SUMIF($A$12:$A$21,"A",$B$12:$B$21)

You can drag down but will have to change the "Name" where you want t
have your formula totals

Cheers
Joe

 

[sachinattri]

Thanks Joey. That would work but that is my last option because I hav
about 3500 Names and they keep getting changed too like add or delet
new names is an ongoing process. I know there is another way t
basically subtotal the groups as per their names I just cannot figur
out how.

Thank you

Sachi

 

[sachinattri]

Thanks Joey. That would work but that is my last option because I hav
about 3500 Names and they keep getting changed too like add or delet
new names is an ongoing process. I know there is another way t
basically subtotal the groups as per their names I just cannot figur
out how.

Thank you

Sachin

May be the formatting of my example was little crooked so it was har
for others to understand. you did though Joey. but for others here i
it again

Column1 - Column2 - Column3 - Column4
A - 10 - -
B - 20 - -
B - 15 - -
C - 30 - -
C - 30 - -
C - 20 - -
D - 50 - -

I want to calculate
Column 3 - which is count all As and calculate the Column2 value
corresponding to A. similarly for others. For e.g. result in the Thir
Row for "B" would be 2 (count) for Col3 and "35" for Col4.

Thanks and hope this is clear now

 

[sachinattri]

Thanks Joey. That would work but that is my last option because I hav
about 3500 Names and they keep getting changed too like add or delet
new names is an ongoing process. I know there is another way t
basically subtotal the groups as per their names I just cannot figur
out how.

Thank you

Sachin

May be the formatting of my example was little crooked so it was har
for others to understand. you did though Joey. but for others here i
it again

Column1 - Column2 - Column3 - Column4
A - 10 - -
B - 20 - -
B - 15 - -
C - 30 - -
C - 30 - -
C - 20 - -
D - 50 - -

I want to calculate
Column 3 - which is count all As and calculate the Column2 value
corresponding to A. similarly for others. For e.g. result in the Thir
Row for "B" would be 2 (count) for Col3 and "35" for Col4.

Thanks and hope this is clear now

 

[Freemini]

If you only want the count and sum to appear at the first occurance o
the name you could set up another formula in the 5th column i.e.

If Col A has your names and Col B your data then in col E enter th
following formula.
=IF(COUNTIF($A$7:A7,A7)=1,A7,"")

note only the first cell is absolute. when this is copied down th
column it will put the name in the cell on the first occurance only.

In col C enter
=COUNTIF($A$7:$A$17,E7)
note here that the absolute cells are the start and finish of you
data.

In col D enter
=SUMIF($A$7:$A$17,E7,$B$7:$B$17)

This will now count and sum all items with the same name, putting th
results alongside the first entry of the name.

If you change the name at any time the formula will still work.

Hope this is what you were looking for.

Mik