Using Array Fromula

[Guest]

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"),'IL'!$Q$4:$Q$196))
Please advise
Thanks

 

[Guest]

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.

 

[Guest]

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

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Thanks Jim
It works

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

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

 

[Max]

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

---