# Thread: find first and last values with conditions

1. ## find first and last values with conditions

Column A is dates in order spanning several years, column B are numbers.
I need the sum of the last number minus the first number for each month and year.

I can set the month/year condition with this:
=SUM(IF(MONTH(\$A:\$A)=9,IF(YEAR(\$A:\$A)=2014,
But I do not know how to find the last and first numbers.
When I use LOOKUP(9E+307,B:B) with above [in an array] it does not return the correct last number for that month.

What I need is:
For month and year, last number minus first number
or
=SUM(IF(MONTH(\$A:\$A)=9,IF(YEAR(\$A:\$A)=2014, last number in column [or last date entered] for that month/year minus first number in column [or first date entered] for that month/year.

2. I have broken the problem down into bits in the attached file. Columns A & B are a set of ascending dates and some random numbers. Column C checks if the date is the first or last of the month and displays the figure and column D is an array formula that finds the next non-blank cell's value, so you can hide those two columns. Column F does the subtraction; column G displays the month that the result applies to. It struggles with the first and last values though.

3. correct this for word wrap where month/year in a column H and formula in col I
=SUMPRODUCT((YEAR(\$A\$2:\$A\$2000)=YEAR(H2))*
MONTH(\$A\$2:\$A\$2000)=MONTH(H2))*\$B\$2:\$B\$2000)

01/2010 630
02/2010 1231
03/2010 2612
04/2010 3112
05/2010 3772
06/2010 5676
07/2010 5760

4. unclehewie,
Thanks. Is there a solution that does not involve the extra columns? Can this be done with a single formula?

dguillett,

Is there an Excel function or approach that can find the first entry within a condition, such as within a date to date period?

5. This will find the first item in col B for the earliest date in Col A.
=MIN(IF(MONTH(\$A\$2:\$A\$80)=MONTH(A3),\$B\$2:\$B\$80))
This is an array formula that must be entered using Ctrl+shift+enter instead of just enter.

6. I found this interesting to do in one column. I don't know if this is overkill or not, but it worked using the file from unclehewie.

It's an array formula that I filled down starting in row TWO.

=IF(MONTH(A2)=MONTH(A3),"",INDEX(\$A\$1:\$B\$500,MAX(( (MONTH(\$A\$2:\$A\$200)+100*YEAR(\$A\$2:\$A\$200))=MONTH(A 3)+100*YEAR(A3))*ROW(\$A\$2:\$A\$200)),2)-INDEX(\$B\$1:\$B\$500,MIN(ROW(),MAX(((MONTH(\$A\$2:\$A\$20 0)+100*YEAR(\$A\$2:\$A\$200))=MONTH(A3)+100*YEAR(A3))* ROW(\$A\$2:\$A\$200)))))

In row 1 I left out the month comparison:

=INDEX(\$A\$1:\$B\$500,MAX(((MONTH(\$A\$2:\$A\$200)+100*YE AR(\$A\$2:\$A\$200))=MONTH(A2)+100*YEAR(A2))*ROW(\$A\$2: \$A\$200)),2)-INDEX(\$B\$1:\$B\$500,MIN(ROW(),MAX(((MONTH(\$A\$2:\$A\$20 0)+100*YEAR(\$A\$2:\$A\$200))=MONTH(A2)+100*YEAR(A2))* ROW(\$A\$2:\$A\$200))))

7. This array formula entered using CSE will find the max and the min and sub tract min from max It is ONE formula in ONE cell

=MAX(IF(((MONTH(\$A\$2:\$A\$1400)=MONTH(A2))(YEAR(\$A\$2 :\$A\$1400)=YEAR(A2))),\$B\$2:\$B\$1400))-MIN(IF(((MONTH(\$A\$2:\$A\$1400)=MONTH(A2))(YEAR(\$A\$2: \$A\$1400)=YEAR(A2))),\$B\$2:\$B\$1400))

8. Doesn't calculate the right answer(s) for me on the sample spreadsheet.

9. What is the correct answer and what is the logic. If you get the max for Jan @ 197 and the min for Jan at 55 and subtract, you get 142. Feb=115, etc

10. Oops. I was using different test numbers. Sorry. Problem is, I think the OP wanted the single results not a difference showing up in each row. So, a comparison of the months like I did would have to apply.

Nice formula.

11. Confusing, OP said

"What I need is:
For month and year, last number minus first number
or
=SUM(IF(MONTH(\$A:\$A)=9,IF(YEAR(\$A:\$A)=2014, last number in column [or last date entered] for that month/year minus first number in column [or first date entered] for that month/year. "

12. Skipro,

Here is a VBA approach that calculates the difference of the adjacent cells in col B between the first and last listed date of each month/year. The code optionally places the monthly differences in col C and a msgbox at the end with the final total. Place in a standard module and run with your data sheet the active sheet.

HTH,
Maud

MonthlyTotals1.png

Code:
```Public Sub MonthlyMaxMin()
'----------------------------------------
'DECLARE AND SET VARIABLES
Dim LastRow As Integer, StartRow As Integer
Dim Total As Double, SubTotal As Double, I As Integer
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
StartRow = 2
'----------------------------------------
'FIND MONTHLY MIN AND MAX DATES
For I = 2 To LastRow
If Month(Cells(I, 1)) <> Month(Cells(I + 1, 1)) Then
Min = WorksheetFunction.Min(Range(Cells(StartRow, 1), Cells(I, 1)))
Max = WorksheetFunction.Max(Range(Cells(StartRow, 1), Cells(I, 1)))
StartRow = I + 1
'----------------------------------------
'VLOOKUP IN COLUMN 2 WITH SUBTOTAL OF DIFFERENCE
SubTotal = WorksheetFunction.VLookup(Max, Range("A2:B" & LastRow), 2, False) - _
WorksheetFunction.VLookup(Min, Range("A2:B" & LastRow), 2, False)
'----------------------------------------
'OUTPUT SBUTOTAL TO COL C AND UPDATE RUNNING TOTAL
Cells(I, 3) = SubTotal  'COMMENT OUT IF SUBTOTALS NOT REQUIRED
Total = Total + SubTotal
End If
Next I
MsgBox Total  'COMMENT OUT IF TOTAL NOT REQUIRED
End Sub```

13. Folks,
You are missing my objective. I do not want the Min/Max, I want the first and last for each month. Column A is dates and is in order, column B is data/numbers and is NOT in ascending or any other order so Min/Max does not return the same as first/last.
See example.
D8 is my entry, not from any formula or script.

Maude,
I have your script included in this example but it is not functioning. I get a syntax error when running macro.
Line error:
Dim Total As Double, SubTotal As Double, I As Integer LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row StartRow = 2

14. Skipro,

The file I provided does total the difference of first and last day of each month and places the value adjacent to the last day in column C. See the image in my above post. They are not max and min differences.

The code you placed in the file was not pasted correctly. Somehow 3 lines of code were pasted on the same line:

Dim Total As Double, SubTotal As Double, I As Integer LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row StartRow = 2

There was also a missing End Sub. See my posted code above. Here is a screen shot of your spreadsheet with the code pasted correctly.

HTH,
Maud
numbers.png

15. Here is a macro that will do it fast with a for i loop

Sub GetDifferenceLastDayLessFirstDay()
Dim lr As Long
Dim i As Long
Application.ScreenUpdating = 0
Columns(3).Clear
lr = Cells(Rows.Count, 1).End(xlUp).Row
On Error Resume Next
For i = lr To 1 Step -1
If Month(Cells(i, 1)) <> Month(Cells(i + 1, 1)) Or _
Cells(i, 1) = "Date" Then
Cells(lr, "c") = Cells(lr, 2) - Cells(i + 1, 2)
lr = i
End If
Next i
Application.ScreenUpdating = 1
End Sub

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•