Using Array Fromula
 

Hi
I want to check condition of 3 columns in my sheet and then add the count of
column and also sum the values in that column
I am using this formula, but it does not work.
=COUNT(IF((TRIM('IL'!$C$4:$C$196)="cp")*(TRIM('IL' !$G$4:$G$196)="ab")*(TRIM('IL'!$L$4:$L$196)="aa"), 'IL'!$Q$4:$Q$196))
=sum(IF((TRIM('IL'!$C$4:$C$196)="cp")*(TRIM('IL'!$ G$4:$G$196)="ab")*(TRIM('IL'!$L$4:$L$196)="aa"),'I L'!$Q$4:$Q$196))
Please advise
Thanks

Using Array Fromula
 

Perhaps just try array-entering both formulas using CTRL+SHIFT+ENTER, instead
of just pressing ENTER. Correctly done, Excel will wrap curly braces { }
around the formula. Visually check for these braces in the formula bar to
confirm that the formula is correctly array-entered.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"ub" wrote:
Hi
I want to check condition of 3 columns in my sheet and then add the count of
column and also sum the values in that column
I am using this formula, but it does not work.
=COUNT(IF((TRIM('IL'!$C$4:$C$196)="cp")*(TRIM('IL' !$G$4:$G$196)="ab")*(TRIM('IL'!$L$4:$L$196)="aa"), 'IL'!$Q$4:$Q$196))
=sum(IF((TRIM('IL'!$C$4:$C$196)="cp")*(TRIM('IL'!$ G$4:$G$196)="ab")*(TRIM('IL'!$L$4:$L$196)="aa"),'I L'!$Q$4:$Q$196))
Please advise
Thanks

Using Array Fromula
 

You want to use SumProduct. Check out this link...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"ub" wrote:

Hi
I want to check condition of 3 columns in my sheet and then add the count of
column and also sum the values in that column
I am using this formula, but it does not work.
=COUNT(IF((TRIM('IL'!$C$4:$C$196)="cp")*(TRIM('IL' !$G$4:$G$196)="ab")*(TRIM('IL'!$L$4:$L$196)="aa"), 'IL'!$Q$4:$Q$196))
=sum(IF((TRIM('IL'!$C$4:$C$196)="cp")*(TRIM('IL'!$ G$4:$G$196)="ab")*(TRIM('IL'!$L$4:$L$196)="aa"),'I L'!$Q$4:$Q$196))
Please advise
Thanks

Using Array Fromula
 

I have tried using CTRL+Shift+Enter and i see {} around the formula, but
still it does not work.

"Max" wrote:

Perhaps just try array-entering both formulas using CTRL+SHIFT+ENTER, instead
of just pressing ENTER. Correctly done, Excel will wrap curly braces { }
around the formula. Visually check for these braces in the formula bar to
confirm that the formula is correctly array-entered.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"ub" wrote:
Hi
I want to check condition of 3 columns in my sheet and then add the count of
column and also sum the values in that column
I am using this formula, but it does not work.
=COUNT(IF((TRIM('IL'!$C$4:$C$196)="cp")*(TRIM('IL' !$G$4:$G$196)="ab")*(TRIM('IL'!$L$4:$L$196)="aa"), 'IL'!$Q$4:$Q$196))
=sum(IF((TRIM('IL'!$C$4:$C$196)="cp")*(TRIM('IL'!$ G$4:$G$196)="ab")*(TRIM('IL'!$L$4:$L$196)="aa"),'I L'!$Q$4:$Q$196))
Please advise
Thanks

Using Array Fromula
 

Try this quick sample which shows your 2 arrays are in working order:
http://cjoint.com/?gBrxaspdX8
ub_misc.xls

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"ub" wrote:
I have tried using CTRL+Shift+Enter and i see {} around the formula, but
still it does not work.

Using Array Fromula
 

Thanks Jim
It works

"Jim Thomlinson" wrote:

You want to use SumProduct. Check out this link...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"ub" wrote:

Hi
I want to check condition of 3 columns in my sheet and then add the count of
column and also sum the values in that column
I am using this formula, but it does not work.
=COUNT(IF((TRIM('IL'!$C$4:$C$196)="cp")*(TRIM('IL' !$G$4:$G$196)="ab")*(TRIM('IL'!$L$4:$L$196)="aa"), 'IL'!$Q$4:$Q$196))
=sum(IF((TRIM('IL'!$C$4:$C$196)="cp")*(TRIM('IL'!$ G$4:$G$196)="ab")*(TRIM('IL'!$L$4:$L$196)="aa"),'I L'!$Q$4:$Q$196))
Please advise
Thanks

Using Array Fromula
 

ub,

I probably might not hear from you again, but felt compelled to post a
clarification here.

If sumproduct works for you on the data-sets that you have, there's no
reason why your original 2 conditional array formulas won't or didn't, as
per the quick sample provided in my 2nd response in the other branch.

The key facility/difference is that sumproduct works on conditional arrays
but doesn't* require array-entering while your original array formulas need
array-entering.
*except where TRANSPOSE is used within
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---