PDA

View Full Version : Sum of column



pawasthi
06-27-2011, 04:41 PM
Hi All,

I have three columns (A,B,C) in sheet and I need to sum column C data where data in A = *AB* AND data in column B is "*ype1*"

It means I need to get the count of such rows using wild characters.


A B C
ABC type1 1
DEF type2 0
ABC type2 1
XYZ type1 1
ABC type1 0
DEF type1 0


Thanks

p45cal
06-27-2011, 05:25 PM
=SUMIFS($C$2:$C$7,$B$2:$B$7,"*ype1*",$A$2:$A$7,"*AB*")

pawasthi
06-27-2011, 09:36 PM
Thanks "p45cal"....It perfectly worked... :)

pawasthi
06-28-2011, 11:17 AM
Hello all,

It is working perfectly alright in Office 2010 but as SUMIFS is not available in Office 2003, its not working in Office 2003.

Please suggest how I can make it work with 2003 using wild characters.

regards
Peeyush

p45cal
06-28-2011, 12:09 PM
ARRAY-ENTER (use Ctrl+Shift + Enter, NOT just Enter, to commit the formula to the sheet):
=SUM(IF(ISERROR(FIND("AB",$A$2:$A$7)),,IF(ISERROR(FIND("ype1",$B$2:$B$7)),,$C$2:$C$7)))

it is case-sensitive, to make it case-insensitive, replace FIND with SEARCH.

No wildcards needed.

pawasthi
06-29-2011, 10:18 AM
Thanks p45cal...It worked..

Could you please help me in finding the count of unique values in columns.

I tried the below but its displaying #N/A (I need this for Office 2003 & 2010)
=SUM(IF(FREQUENCY(MATCH(R402:R1500,R402:R1500,0),MATCH(R402:R1500,R402:R150 0,0))>0,1))

pawasthi
06-29-2011, 10:57 AM
Hi All,

The below code worked for me.

=SUMPRODUCT((R402:R1500<>"")/COUNTIF(R402:R1500,R402:R1500&""))

But how I can club the two codes now to give the below results:

1. Get the count from below code
=SUM(IF(ISERROR(FIND("AB",$A$2:$A$7)),,IF(ISERROR(FIND("ype1",$B$2:$B$7)),, $C$2:$C$7)))

2. Get the unique value count for the above mentioned condition in point 1 and then add that to result from point 1. So I need the unique value count on below conditions:

IF(ISERROR(FIND("AB",$A$2:$A$7)),,IF(ISERROR(FIND("ype1",$B$2:$B$7))

Thanks