3

I have a formula that searches for a column header on another sheet, then once its found it goes through some sumifs. My data roughly looks like this:

    1                          8            10                      11         12
Column E  ...  Column N ... Column O ... Column AB   Column AC   Column AD   Column AE

Existing        CCS           data         100.00      100.00     120.00      150.00

The numbers above the column names are significant and I use them to designate columns that are relevant to my formulas. Now, There are really only a few cells that are significant to the loop. Cell A1 has the number of the column I want to terminate on. Cell B1 has the number of the column I want to start from. So If I choose A1=8 and B1=1, I want the below code to run the sumifs starting by searching for column 1, then for column 2, all the way until column 8 and add all of the values from each loop together and give me the total. Using the above table as an example, if I set A1="12" and B1="10", I want it to spit out 370.00.

The formula I currently have is this:

=SUMIFS(INDEX('Program Data'!$A:$GA,0,MATCH(A1,'Data'!$1:$1,0)),'Data'!$N:$N,"CCS", 'Data'!$E:$E,"Existing")+SUMIFS(INDEX('Data'!$A:$GA,0,MATCH(A1,'Data'!$1:$1,0)), 'Data'!$N:$N,"CCS",'Data'!$E:$E,"Deep")

The formula above only gives me the total for the column that contains the numerical string in A1, but I need a total for all columns with the strings from B1 -> A1.

EDIT

As requested, here is my failing attempt at this loop. Since B1 is essentially always a static number (meaning it will always be 45 or 88 or something), I thought I could create a loop telling it to start at whatever value B1 is and essentially create and drop the formula into the cell as I need it. So for this one, B1=1=z...

Sub LoopingYTD()
Dim z As Integer: z = 1
Dim formulaString2 As String
Do Until z = Range("A1") + 1
    If formulaString2 = "" Then
        formulaString2 = "="
    Else
        formulaString2 = formulaString2 & " + "
    End If

    formulaString2 = formulaString2 + " SUMIFS(INDEX('Data'!A:GA,0,MATCH("
    formulaString2 = formulaString2 & """*"" & 
    formulaString2 = formulaString2 & "z"&" ""
    formulaString2 = formulaString2 & """*""" & ",'Data'!1:1,0)),"
    formulaString2 = formulaString2 & "'Data'!$N:$N,”CCS”,”        
    formulaString2 = formulaString2 & “'Data'!$E:$E,”Existing”))"

    formulaString2 = formulaString2 & "+ SUMIFS(INDEX('Data'!A:GA,0,MATCH("
    formulaString2 = formulaString2 & """*"" & 
    formulaString2 = formulaString2 & "z"&" ""
    formulaString2 = formulaString2 & """*""" & ",'Data'!1:1,0)),"
    formulaString2 = formulaString2 & "'Data'!$N:$N,”CCS”,”        
    formulaString2 = formulaString2 & “Data'!$E:$E, ”Deep”))"
    z = z + 1
Loop
Range("B20").Value = formulaString2

I keep getting an "application-defined or object defined error", though I'm not sure what the remedy is for that.

This is based off of a suggestion from an earlier question I asked. Since then, I've been trying to simplify it, as that was a bit messy, so I just want it to search for a single string of numbers. Basically run the code in the link, but instead of some long, convoluted string based on several cells, I just want it to count up until it reaches the value in A1.

Community
  • 1
  • 1
Kara
  • 135
  • 1
  • 3
  • 12
  • 1
    Please show us what have you tried? Questions asking for code must demonstrate a minimal understanding of the problem being solved.Include attempted solutions, why they didn't work, and the expected results. See also: [Stack Overflow question checklist](http://meta.stackexchange.com/questions/156810/stack-overflow-question-checklist) – Siddharth Rout Nov 13 '13 at 22:37
  • Okay Siddarth, thanks for the checklist -- I will attempt to better clarify. – Kara Nov 14 '13 at 14:08
  • Are these numbers tied to months? It looks like financial data... – Mike L Nov 26 '13 at 21:12
  • Yes, they are! Basicaly its adding together months, but they're not adjacent, so I'm trying to use this numbering sceme to signify accending months. – Kara Nov 26 '13 at 21:13

1 Answers1

1

Well, if B is always a static number, just create a column that holds the possible numbers starting at B and counting up for all possible numbers attached to a month. So if B=20, and A <=32, then the column will look like this (lets say this is column AD):

AC   AD 
Jan  20
Feb  21
...
Dec  32

Then create a formula that compares these numbers to the number you chose in A1 using the Month signifier in AC, and if A1<= (value in column AD), then sum it in. The formula will be kinda long, but will look like this:

=SUM(
 (SUMIFS(INDEX('Program Data'!$A:$GA,0,MATCH($AD1,'Data'!1:1,0)),   
   'Data'!$N:$N,"CCS",'Data'!$E:$E,"Existing")+SUMIFS(INDEX(' 
   'Data'!$A:$GA,0,MATCH($AD1,'Data'!1:1,0)),'Data'!$N:$N,"CCS", 
   'Data'!$E:$E,"Deep"))*(1<=MATCH($A$1,$AC$1:$AC$12,0)),
 (SUMIFS(INDEX('Data'!$A:$GA,0,MATCH($AD2,'Program Data'!1:1,0)),   
   'Data'!$N:$N,"CCS",'Data'!$E:$E,"Existing")+SUMIFS(INDEX(' 
   'Data'!$A:$GA,0,MATCH($AD2,'Data'!1:1,0)),'Data'!$N:$N,"CCS",
   'Data'!$E:$E,"Deep"))*(2<=MATCH($A$1,$AC$1:$AC$12,0)),
 (SUMIFS(INDEX('Data'!$A:$GA,0,MATCH($AD3,'Data'!1:1,0)), 
   'Data'!$N:$N,"CCS",'Data'!$E:$E,"Existing")+SUMIFS(INDEX(
   'Data'!$A:$GA,0,MATCH($AD3,'Data'!1:1,0)),'Data'!$N:$N,"CCS", 
   'Data'!$E:$E,"Deep"))*(3<=MATCH($A$1,$AC$1:$AC$12,0)),
  ....,....,....
 (SUMIFS(INDEX('Data'!$A:$GA,0,MATCH($AD12,'Data'!1:1,0)),
   'Data'!$N:$N,"CCS",'Data'!$E:$E,"Existing")+SUMIFS(INDEX( 
   'Data'!$A:$GA,0,MATCH($AD12,'Data'!1:1,0)), 
   'Data'!$N:$N,"CCS",'Data'!$E:$E,"Deep"))*(12<=MATCH($A$1,$AC$1:$AC$12,0)))

Where A1 = the number you want to stop at. Note that you change the number for the matches at the end of the statement to each ascending month number (1-12, not the numbers assigned). This way it looks good for the user (who wouldn't necessarily understand the numbers anyway) and will still output your desired result.

Mike L
  • 486
  • 5
  • 16
  • 33