Solved: How do you count this?

[wolf.stalker]

hey all, thanks for any help you may be able to provide me on this!

i have been working on this for a good 6hrs straight today so i am getting fuzzy here. let me see if i can simplify this.

9-10 9-11 9-12
HilowHilowHiCity A 9585 9688 94City B 97 78 9575 99 City C 102 43 10055 102 City A 89 65 88 6590 City E 93 60 9266 93 City A 77 63 7763 77 City GCity CCity I

is there a way in vba that i can get a sum of every other colmun based on the row identifier? (City A for example?). this will also need to be within a range of days which means i might start on column 2 or 22.

so, A - High total would = 769 from 9-10 to 9-12

i suspect after i have my first cold beer at home, i will start getting some ideas but i am always open to learning something new that maybe i have not thought of before!

thanks again folks!

[Simon Lloyd]

Can you supply a sample workbook as your data didn't travel well?

[shawnhet]

I think you need to post an example sheet here. I can't make sense of what you are trying to do.

Cheers,

[wolf.stalker]

I think you need to post an example sheet here. I can't make sense of what you are trying to do.

Cheers,

i will be glad too! yeah, that looks like crap the way it came out...

[Simon Lloyd]

Right, i've written the code as per your example but you can adapt it, assuming that the date you want to start the look from is in I2, the end date is in J2 and the term (Hi) is in K2 the answer will be put in L2
[VBA]Sub Count_Terms()
Dim LastCol As Long
Dim FirstCol As Long
Dim i As Long
Dim Ans As Long
Ans = 0
FirstCol = Cells.Find(What:=Range("I2"), After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column
LastCol = Cells.Find(What:=Range("J2"), After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column
For i = FirstCol To LastCol Step 1
If Cells(2, i).Value = Range("K2") Then
Ans = Ans + Application.WorksheetFunction.Sum(Range(Cells(3, i).Address & ":" & Cells(Rows.Count, i).End(xlUp).Address))
End If
Next i
Range("L2") = Ans
End Sub[/VBA]

[wolf.stalker]

Simon -

Thanks for the code! my worksheet that i need to "count" is rather ....well long. i think i use soomething like up to column BBN ? lol but only need about 300 rows....

if i am super lucky i will get this working tomorrow.

thanks again for the input!

[Simon Lloyd]

In that case (and as i misread your initial requirement) try this but you need to use A301 for your Name to search i.e City A, B301 for start date, C301 for end date and D301 for your column name i.e Hi, the result will be put in E301[vba]Sub Count_Terms()
Dim LastCol As Long
Dim FirstCol As Long
Dim i As Long
Dim Ans As Double
Dim MyCell As Range
Ans = 0
FirstCol = Cells.Find(What:=Range("B301"), After:=Range("A1")).Column
LastCol = Cells.Find(What:=Range("C301"), After:=Range("A1")).Column
For i = FirstCol To LastCol Step 1
If Cells(2, i).Value = Range("D301") Then
For Each MyCell In Range(Cells(3, i).Address & ":" & Cells(300, i).Address)
If Range("A" & MyCell.Row) = Range("A301") Then
Ans = Application.WorksheetFunction.Sum(Ans, MyCell.Value)
End If
Next MyCell
End If
Next i
Range("E301") = Ans
Range("E301").NumberFormat = "#,##00.000_);(#,##00.000)"
End Sub[/vba]

[macropod]

You could do the same thing without vba, using an array formula. For the sample spreadsheet, the formula would be:
=SUM(IF((A3:A8=A3)*(B2:G2=B2),B3:G8))
entered with Ctrl-Shift-Enter.

Alternatively, an ordinary SUMPRODUCT formula would do the job:
=SUMPRODUCT((A3:A8=A3)*(B2:G2=B2),B3:G8)

[Simon Lloyd]

Macropod, he'd have a bit of a time with that as his worksheet is up to column BBN, i'm also not sure that it is always every other column.

[macropod]

Macropod, he'd have a bit of a time with that as his worksheet is up to column BBN, i'm also not sure that it is always every other column.

Hi Simon,

The 'every other column' stipulation is irrelevant to the formulae, both of which simply look for rows with 'HiCity A' and columns headed with 'Hi'.

If there is a great amount of data (columns*rows) and the cpu is slow, then, yes, either approach could take a while to calculate.

[Simon Lloyd]

Hi Simon,

The 'every other column' stipulation is irrelevant to the formulae, both of which simply look for rows with 'HiCity A' and columns headed with 'Hi'.

If there is a great amount of data (columns*rows) and the cpu is slow, then, yes, either approach could take a while to calculate.

I appreciate that, my first thought was to ask why a vba solution rather than formulae, but then i just offered what the OP asked for, however, i re-read it and realised that he would want to stipulate between which dates to pick up the data too which is why i again posted code, but i do agree formulae is faster

[Bob Phillips]

You will also need to copy the dates into the alternate column headers for this to work

=SUMPRODUCT(($A$3:$A$8="City A")*($B$2:$G$2="Low")*($B$1:$G$1>=--"2010-09-11")*($B$1:$G$1<=--"2010-09-12")*($B$3:$G$8))

[Simon Lloyd]

You will also need to copy the dates into the alternate column headers for this to work

=SUMPRODUCT(($A$3:$A$8="City A")*($B$2:$G$2="Low")*($B$1:$G$1>=--"2010-09-11")*($B$1:$G$1<=--"2010-09-12")*($B$3:$G$8))

Bob, to keep that more flexible could the op use reference cells rather than hard coding the lookup name and dates?

[Bob Phillips]

Of course, but they would need to identify where.

[macropod]

Here's a formula that incorporates the date info without the need for adding the dates to the intermediate columns:
=SUMPRODUCT((A3:A8=A3)*(B2:G2=B2)*((OFFSET(B1:G1,,-1)>=D1)+(B1:G1>=D1))*((OFFSET(B1:G1,,-1)<=F1)+(B1:G1<=F1)),B3:G8/2)
It works on the assumption that the date always appears over the 'Hi' column only.
FWIW, the A3 reference is for the city name (change the row for another city), the B2 reference is for the Hi/Low value (change the column for another value), the D1 references are for the earliest date (change the column for another date) and the F1 references are for the latest date (change the column for another date).

[wolf.stalker]

thanks everyone for the input!

i can share a little more detail but i think the solotions you have provided will work fine. my column headers are basicly dates from 8-29-10 to 8-29-11 where the date is in every other column. underneath each "physical date" will be one value that i need to count <like "HI"> and in the blank column header column next to it, i will want to catch the second value <like "LO">.

normally i am going to count values for a 28 day period however the user will (at some point) have the flex to be about to count ANY date range.

again, thank you guys for all your help.

[wolf.stalker]

In that case (and as i misread your initial requirement) try this but you need to use A301 for your Name to search i.e City A, B301 for start date, C301 for end date and D301 for your column name i.e Hi, the result will be put in E301[vba]Sub Count_Terms()
Dim LastCol As Long
Dim FirstCol As Long
Dim i As Long
Dim Ans As Double
Dim MyCell As Range
Ans = 0
FirstCol = Cells.Find(What:=Range("B301"), After:=Range("A1")).Column
LastCol = Cells.Find(What:=Range("C301"), After:=Range("A1")).Column
For i = FirstCol To LastCol Step 1
If Cells(2, i).Value = Range("D301") Then
For Each MyCell In Range(Cells(3, i).Address & ":" & Cells(300, i).Address)
If Range("A" & MyCell.Row) = Range("A301") Then
Ans = Application.WorksheetFunction.Sum(Ans, MyCell.Value)
End If
Next MyCell
End If
Next i
Range("E301") = Ans
Range("E301").NumberFormat = "#,##00.000_);(#,##00.000)"
End Sub[/vba]

i do have one question regarding this. my FirstCol = value in B301 and our routine starts looking from cell A1.

is there a way to limit (i suspect there is i just don't know it) the range we look in? i only want to look in say the first 100 rows, not the entire sheet. this doable?

[Bob Phillips]

Simon's code is alraedy limited to row 300, For Each MyCell In Range(Cells(3, i).Address & ":" & Cells(300, i).Address)

[wolf.stalker]

ahh! now i see that. duh...lol thanks

[Simon Lloyd]

i do have one question regarding this. my FirstCol = value in B301 and our routine starts looking from cell A1.

Where you say start looking from A1 this is purely to reset where the FIND function will start looking from, as we are only looking for a date which you, by way of example, will only have one instance of on the sheet, FirstCol doesn't equal B301, B301 would be the first date to look for.

is there a way to limit (i suspect there is i just don't know it) the range we look in? i only want to look in say the first 100 rows, not the entire sheet. this doable?

As xld pointed out, the code is limited to look down as far as row 300 as per your statement of how many rows you will use (which is why the look up information and result are in row 301). The summing....etc isn't over te entire sheet just up to row 300 in the columns between the 2 dates you give in B310 and C301.