countif and sumif help

[tarun_1]

Hi,


I have got b/m data in columns A,B,C

Options	Sum	Country

[code]A	500	India

[code]B	400	India

[code]C	600	India

[code]D	400	India

[code]A	500	Others

[code]E	200	India

[code]A	400	India

B 300 Others[/code]

B 600 Others[/code]

C 500 Others[/code]


I want to calculate -

Count of A,B & C

Sum of A,B & C

Count of India - A,B & C

Sum of India A,B & C


The formula that i am using for each is:


Count of A,B & C =COUNTIF($A$2:$A$11,"A&#34+COUNTIF($A$2:$A$11,"B&#34+COUNTIF($A$2:$A$11,"C&#34[/code]


Sum of A,B & C =SUMIF($A$2:$A$11,"A",$B$2:$B$11)+SUMIF($A$2:$A$11,"b",$B$2:$B$11)+SUMIF($A$2:$A$11,"c",$B$2:$B$11)[/code]


Count of India - A,B & C =COUNTIFS($A$2:$A$11,"A",$C$2:$C$11,"India&#34+COUNTIFS($A$2:$A$11,"b",$C$2:$C$11,"India&#34+COUNTIFS($A$2:$A$11,"c",$C$2:$C$11,"India&#34[/code]


Sum of India A,B & C =SUMIFS($B$2:$B$11,$A$2:$A$11,"A",$C$2:$C$11,"India&#34+SUMIFS($B$2:$B$11,$A$2:$A$11,"b",$C$2:$C$11,"India&#34+SUMIFS($B$2:$B$11,$A$2:$A$11,"c",$C$2:$C$11,"India&#34[/code]


But i think - though - i am getting desired result - this is not good formula.


Can you suggest a good formula to get the desired result?


<Regards>

Tarun

 

[NARAYANK991]

Hi Tarun ,


The count of "A" , "B" and "C" in column A , can be got by :


=SUM(IF(A2:A11={"A","C"},1,0))


entered as an array formula , using CTRL SHIFT ENTER.


I think you can use this same idea for the other queries.


Narayan

 

[tarun_1]

Hi Narayan,


First you told


Second i got it:

=SUM(IF(A2:A11={"A","B","C"},B2:B11,0))


but not getting 3rd and 4th:


Count of India - A,B & C

Sum of India A,B & C


<Regards>

Tarun

 

[NARAYANK991]

Hi Tarun ,


Adding a second criterion is a matter of adding a second IF statement :


=SUM(IF(A2:A11={"A","B","C"},IF(C2:C11="India",1,0)))


Enter as an array formula.


Narayan

 

[tarun_1]

Hi Narayan,


wow... thanks...


<Regards>

Tarun