Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
carl
 
Posts: n/a
Default Modify SumIF... Array Formula

I am using this formula.

=SUM(IF((TMS2!$BL$3:$BL$60000<=WatchList!$A$4)*(TM S2!$K$3:$K$60000="050S0F1");TMS2!$F$3:$F$60000))

Is it possible to modify this part (TMS2!$K$3:$K$60000="050S0F1") to set the
criteria to look for all cases where the cell equals 050S0F1, 050S0F2 ...
050S0FB. In other words, any cell where the left 6 characters are 050S0F ?

Thank you in advance.
  #2   Report Post  
Alok
 
Posts: n/a
Default

Replace ; with a comma in LEFT() ?

Alok Joshi

"Peo Sjoblom" wrote:

It is better to use sumproduct and enter the formula normally

=SUMPRODUCT(--(TMS2!$BL$3:$BL$60000<=WatchList!$A$4);--(LEFT(TMS2!$K$3:$K$60000;6)="050S0F");TMS2!$F$3:$F $60000)


Regards,

Peo Sjoblom

"carl" wrote:

I am using this formula.

=SUM(IF((TMS2!$BL$3:$BL$60000<=WatchList!$A$4)*(TM S2!$K$3:$K$60000="050S0F1");TMS2!$F$3:$F$60000))

Is it possible to modify this part (TMS2!$K$3:$K$60000="050S0F1") to set the
criteria to look for all cases where the cell equals 050S0F1, 050S0F2 ...
050S0FB. In other words, any cell where the left 6 characters are 050S0F ?

Thank you in advance.

  #3   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

No, look at the OP's original post, he's obvisoulsy using Eurpoean delimiters

Regards,

Peo Sjoblom

"Alok" wrote:

Replace ; with a comma in LEFT() ?

Alok Joshi

"Peo Sjoblom" wrote:

It is better to use sumproduct and enter the formula normally

=SUMPRODUCT(--(TMS2!$BL$3:$BL$60000<=WatchList!$A$4);--(LEFT(TMS2!$K$3:$K$60000;6)="050S0F");TMS2!$F$3:$F $60000)


Regards,

Peo Sjoblom

"carl" wrote:

I am using this formula.

=SUM(IF((TMS2!$BL$3:$BL$60000<=WatchList!$A$4)*(TM S2!$K$3:$K$60000="050S0F1");TMS2!$F$3:$F$60000))

Is it possible to modify this part (TMS2!$K$3:$K$60000="050S0F1") to set the
criteria to look for all cases where the cell equals 050S0F1, 050S0F2 ...
050S0FB. In other words, any cell where the left 6 characters are 050S0F ?

Thank you in advance.

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
Formula to count number of dates in an array Lilasviolet Excel Worksheet Functions 2 April 7th 05 07:44 PM
Array Formula frankybenali Excel Worksheet Functions 1 February 16th 05 06:37 PM
What instead of an array formula part 2 Reg Besseling Excel Discussion (Misc queries) 2 December 10th 04 07:35 AM
What instead of an array formula? Reg Besseling Excel Discussion (Misc queries) 3 December 6th 04 01:55 PM
Modify SUMIF and COUNTIF to work with SUBTOTALS SSHO_99 Excel Worksheet Functions 2 November 12th 04 11:36 PM


All times are GMT +1. The time now is 06:29 AM.

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

About Us

"It's about Microsoft Excel"