#1   Report Post  
Posted to microsoft.public.excel.misc
UB UB is offline
external usenet poster
 
Posts: 120
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,939
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.misc
UB UB is offline
external usenet poster
 
Posts: 120
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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.



  #6   Report Post  
Posted to microsoft.public.excel.misc
UB UB is offline
external usenet poster
 
Posts: 120
Default 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

  #7   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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
---


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Fromula tboy Excel Discussion (Misc queries) 2 May 29th 07 02:17 PM
Array Fromula Pmxgs Excel Worksheet Functions 6 November 1st 06 10:05 PM
How can I write a fromula to subtract 1/4 from 1/2 and so on? Excell novice Excel Discussion (Misc queries) 5 September 17th 06 12:41 AM
Corporated into the same fromula please. Steved Excel Worksheet Functions 5 October 12th 05 12:10 AM
Need a fromula comotoman Excel Discussion (Misc queries) 3 October 3rd 05 05:00 PM


All times are GMT +1. The time now is 02:15 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"