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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.