Array using LEN to specify selection criteria

[mitchv3]

I am currently working with some variable data that requires several sum ifs to get a final workable number.

I can get most of them, but am having trouble selecting only the data that is 13 characters long in Column F.

Below is what I currently have (that works). I assume I need to use LEN for column F, but cannot seem to get it to work correctly.

{=SUM(IF(A:A=$M$2,1,0)*(SUM(IF(B$3:B$65536=$N$2:$T$2,1,0)*(K$3:K$65536)))/SUM(IF(A:A=$M$2,1,0)*SUM(IF(B$3:B$65536=$N$2:$T$2,1,0)*(G$3:G$65536))))}


This is my first post, so let me know if there is more info needed and I will be glad to add, but trying to keep this as short as possible.

Thanks,

 

[Aladin Akyurek]

I am currently working with some variable data that requires several sum ifs to get a final workable number.

I can get most of them, but am having trouble selecting only the data that is 13 characters long in Column F.

Below is what I currently have (that works). I assume I need to use LEN for column F, but cannot seem to get it to work correctly.

{=SUM(IF(A:A=$M$2,1,0)*(SUM(IF(B$3:B$65536=$N$2:$T$2,1,0)*(K$3:K$65536)))/SUM(IF(A:A=$M$2,1,0)*SUM(IF(B$3:B$65536=$N$2:$T$2,1,0)*(G$3:G$65536))))}


This is my first post, so let me know if there is more info needed and I will be glad to add, but trying to keep this as short as possible.

Thanks,

If you are not on Excel 2007 or later, you must avoid referencing whole columns...

Guessing from the formula you posted...

Control+shift+enter, not just enter:

=SUM(
   IF(A$3:A$400=$M$2,
   IF(ISNUMBER(MATCH(B$3:B$400,$N$2:$T$2,0)),
    K$3:K$400)))/
  SUM(
   IF(A$3:A$400=$M$2,
   IF(ISNUMBER(MATCH(B$3:B$400,$N$2:$T$2,0)),
    G$3:G$400)))

 

[mitchv3]

That is a lot cleaner.

But how would I add in the check to see if Column F LEN = $M$1 then do the remainder of the calculation?

 

[Aladin Akyurek]

That is a lot cleaner.

But how would I add in the check to see if Column F LEN = $M$1 then do the remainder of the calculation?

Do you mean adding a LEN test as a term in both the nominator and denominator? If so...

=SUM(
   IF(A$3:A$400=$M$2,
   IF(ISNUMBER(MATCH(B$3:B$400,$N$2:$T$2,0)),
   IF(LEN(F$3:F$400)=$M$1,
    K$3:K$400)))/
  SUM(
   IF(A$3:A$400=$M$2,
   IF(ISNUMBER(MATCH(B$3:B$400,$N$2:$T$2,0)),
   IF(LEN(F$3:F$400)=$M$1,
    G$3:G$400)))

 

[mitchv3]

Thank you! That works great

 

[Aladin Akyurek]

Thank you! That works great

You are welcome. Thanks for providing feedback.