Please help with Array formula

[SBlasberg]

Hello,

I'm attempting to reduce the size of my sheet, and hoping to be able to pull this off in one cell. Here's a simplified version of my sheet:



First, I'd like to pull the (1) left most digits from A6:A11 and multiply each by their % counterpart in column D. Then, I'd like to sum those results using subtotal- so it reflects filtering. Then, I'd like to multiply that sum by the number in cell A4.

I'd like to have that same process done for columns B & C using the same %'s in D, and then add together the three subtotals. I'm hoping all of this can be done in one cell- until now, I've done each of these steps separately in their own cells, but sheet has gotten too big.

Thanks so much!

 

[Johnny C]

This may or may not help... you need to load it into an add-in, but once there you can use the PIECE function to extract any part of a delimited list.

The format for using it is:-
=PIECE(Search string, delimiter, 1st (or start) occurrence between parameters, optional end occurrence

If you're pulling pieces of text apart don't forget Excel will treat them as text so you need to wrap them in VALUE()

so your one-cell formula would be =((VALUE(PIECE(A6,",",1))+VALUE(PIECE(B6,",",1))+VALUE(PIECE(B6,",",1)))*D6

Here's the VBA to add to an add-in:

Function Piece(Searchstring$, Separator$, Index1%, Optional Index2%) As String
Dim t, IndexCount%
Piece = ""
If Index2 <> 0 And Index2 < Index1 Then
  Piece = "#PARAM"
  Exit Function
End If

t = Split(Searchstring, Separator)
If UBound(t) = 0 Then
  Piece = Searchstring
  Exit Function
End If
If UBound(t) + 1 < Index1 Then Exit Function
If UBound(t) + 1 < Index2 Then Index2 = UBound(t) + 1
If Index2 = 0 Or Index2 <= Index1 Then
  If UBound(t) > 0 Then Piece = t(Index1 - 1)
Else
  For IndexCount = Index1 To Index2
  Piece = Piece & t(IndexCount - 1)
  If IndexCount <> Index2 Then Piece = Piece & Separator
  Next IndexCount
End If

End Function

 

[Hui]

For Column A:

If there is always a single digit before the ,
=SUMPRODUCT(VALUE(LEFT(A6:A11,1)),$D$6:$D$11)*A4

If there can be multiple digits before the ,
=SUMPRODUCT(VALUE(LEFT(A6:A11,FIND(",",A6:A11)-1)),$D$6:$D$11)*A4

 

[SBlasberg]

For Column A:

If there is always a single digit before the ,
=SUMPRODUCT(VALUE(LEFT(A6:A11,1)),$D$6:$D$11)*A4

If there can be multiple digits before the ,
=SUMPRODUCT(VALUE(LEFT(A6:A11,FIND(",",A6:A11)-1)),$D$6:$D$11)*A4

Thanks so much for this, it's exactly what I'm looking for! My only follow up is that I noticed that there has to be a value in every single cell between A6:A11 for this to work. My column has rows with some A cells left blank- is there a way have this ignore those blank cells without returning an error?

Thanks!

 

[SBlasberg]

Thanks so much for this, it's exactly what I'm looking for! My only follow up is that I noticed that there has to be a value in every single cell between A6:A11 for this to work. My column has rows with some A cells left blank- is there a way have this ignore those blank cells without returning an error?

Thanks!

I also noticed that it does not subtotal when I filter- is there a way to enable that as well? Thanks so much!

 

[John Jairo V]

Hi to all!

For column A, try this formulae (CSE):

=SUM(SUBTOTAL(3,OFFSET(A6:A11,ROW(A6:A11)-ROW(A6),,1))*TRUNC(SUBSTITUTE(IF(A6:A11="",,A6:A11),",","."))*D6:D11)*A4

Blessings!

 

[SBlasberg]

Hi to all!

For column A, try this formulae (CSE):

=SUM(SUBTOTAL(3,OFFSET(A6:A11,ROW(A6:A11)-ROW(A6),,1))*TRUNC(SUBSTITUTE(IF(A6:A11="",,A6:A11),",","."))*D6:D11)*A4

Blessings!

This is exactly what I'm looking for, thank you!!!

Can I ask how you would adjust this formula to pull numbers to the right of the comma, in A6:A11?

Thanks!

 

[John Jairo V]

Hi again!

Try (CSE):

=SUM(SUBTOTAL(3,OFFSET(A6:A11,ROW(A6:A11)-ROW(A6),,1))*IF(A6:A11="",,MID(A6:A11,1+FIND(".",A6:A11),99))*D6:D11)*A4

Blessings!

 

[SBlasberg]

Hi again!

Try (CSE):

=SUM(SUBTOTAL(3,OFFSET(A6:A11,ROW(A6:A11)-ROW(A6),,1))*IF(A6:A11="",,MID(A6:A11,1+FIND(".",A6:A11),99))*D6:D11)*A4

Blessings!

This seems to give me a VALUE error- and it's well beyond my capabilities to troubleshoot. Thanks!

 

[John Jairo V]

Sorry, I made a mistake in the formula.

Try (CSE):
=SUM(SUBTOTAL(3,OFFSET(A6:A11,ROW(A6:A11)-ROW(A6),,1))*IF(A6:A11="",,MID(A6:A11,1+FIND(",",A6:A11),99))*D6:D11)*A4

Blessings!

 

[SBlasberg]

Sorry, I made a mistake in the formula.

Try (CSE):
=SUM(SUBTOTAL(3,OFFSET(A6:A11,ROW(A6:A11)-ROW(A6),,1))*IF(A6:A11="",,MID(A6:A11,1+FIND(",",A6:A11),99))*D6:D11)*A4

Blessings!

Thanks so much for your help! This is perfect.