filter and sum values based on three selection from dropdown

hello
i try finding code show data when i select from e1,f1,g1 and sum values the lastrow this is what i got but doesn't work

PHP Code:

---

Sub AAA()    Dim R As Range   
 Dim DataRange As Range   
 Dim Total As Double   
 Dim TheYear As String    
Dim TheMonth As String    
Dim Theitem As String       ' get your values from the linked cells.    
TheYear = Range("A1").Value   
 TheMonth = Range("F1").Value    
Theitem = Range("G1").Value      
 Set DataRange = Worksheets("INV").Range("A1:D10") '<< CHANGE   
 For Each R In DataRange.Columns(1).Cells        
If R(1, 1).Value = TheYear Or LCase(Trim(TheYear)) = "ALL" Then           
 If R(1, 2).Value = TheMonth Or LCase(Trim(TheMonth)) = "ALL" Then              
  If R(1, 3).Value = Theitem Or LCase(Trim(Theitem)) = "ALL" Then                    
Total = Total + R(1, 4).Value          
      End If           
 End If        
End If    
Next R    
MsgBox Total
End Sub 


---

thanks in advance

Excel VBA Programming For Dummies by John Walkenbach | 9781119077398 | Paperback | Barnes & Noble

Possibly a more helpful suggestion ...

1. In ...

Code:

---

LCase(Trim(TheYear)) = "ALL"

---

... since you lower case TheYear, it will NEVER, EVER, EVER = "ALL"


2. In ...

Code:

---

TheYear = Range ("A1")

---

... it should be E1


3. Brute force, and not very elegant, but easiest to understand

Code:

---


Option Explicit


Private Sub GetTotal()
    Dim iRow As Long
    Dim Total As Double
    Dim TheYear As Variant, TheMonth As Variant
    Dim Theitem As String
 
   
    With ActiveSheet
        ' get your values from the linked cells.
        TheYear = .Range("E1").Value
        TheMonth = .Range("F1").Value
        Theitem = .Range("G1").Value
       
        For iRow = 2 To ActiveSheet.Cells(1, 1).CurrentRegion.Rows.Count
            If .Cells(iRow, 1).Value = TheYear Or LCase(Trim(TheYear)) = "all" Then
                If .Cells(iRow, 2).Value = TheMonth Or LCase(Trim(TheMonth)) = "all" Then
                    If .Cells(iRow, 3).Value = Theitem Or LCase(Trim(Theitem)) = "all" Then
                        Total = Total + .Cells(iRow, 4).Value
                    End If
                End If
            End If
        Next iRow
    End With
   
    MsgBox Total
End Sub

---

hi, paul actually updating code works only when select all but when i select month like May or select control like ast it gives me 0 and i would summing value in the last row

There were some inconsistencies with UC/LC (MAY - may) and variable types (string -vs double)

Attachment 27469

This should fix it

Code:

---


Option Explicit
Option Compare Text


Sub GetTotal()
    Dim iRow As Long
    Dim Total As Double
    Dim TheYear As Variant, TheMonth As Variant
    Dim Theitem As String
 
   
    With ActiveSheet
        ' get your values from the linked cells.
        TheYear = Trim(.Range("E1").Value)
        TheMonth = Trim(.Range("F1").Value)
        Theitem = Trim(.Range("G1").Value)
       
        For iRow = 2 To .Cells(1, 1).CurrentRegion.Rows.Count
            If CStr(.Cells(iRow, 1).Value) = TheYear Or TheYear = "all" Then
                If .Cells(iRow, 2).Value = TheMonth Or TheMonth = "all" Then
                    If .Cells(iRow, 3).Value = Theitem Or Theitem = "all" Then
                        Total = Total + .Cells(iRow, 4).Value
                    End If
                End If
            End If
        Next iRow
       
        .Cells(1, 4).End(xlDown).Offset(1, 0).Value = Total
   
    End With
End Sub

---

Edit - added total to last row

nice updating thanks paul about the total i would be under last row is filling not every time i select from dropdown it move the total under it as your picture the total should in d9 if i change selection it moves d10 this is not what i would i want keeping the total in d9

Quote:

---

Originally Posted by maghari

nice updating thanks paul about the total i would be under last row is filling not every time i select from dropdown it move the total under it as your picture the total should in d9 if i change selection it moves d10 this is not what i would i want keeping the total in d9

---

Replace

Code:

---

.Cells(1, 4).End(xlDown).Offset(1, 0).Value = Total

---

with

Code:

---

.Cells(9, 4).Value = Total

---

If you add more data rows, this will replace whatever is in row 9, but if that's what you want ....

thanks paul but i note if i add another year 2012,2013..etc it doesn't sum it gives me 0 do you have any idea:think:

In your example workbook, there is only 2011 in the data and in the validation rules

Attachment 27471


Just changing some data and updating the data validation, it seems to work

Attachment 27472

Attach a new example workbook with what ever you're seeing

yes you're right my mistake when i select a new year i selected another options are not existed that's why gives me 0 so i have last thing i would summing dynamically as you see maybe filling data changes after row 9

HTML Code:

---

.Cells(9, 4).Value = Total

---

in this case this is not useful if is possible sum after lastrow filling data maybe after 12 or 15 and so on it based filling data :yes

Maybe

Code:

---

.Cells(3, 6).Value = Total


---

thanks paul but this is not what i want just after lastrow contains value for instance if the lastrow in d9 then show summing in d10 if the lastrow in d11 then summing in d12 it is based on filling lastrow :help

Attachment 27474

Code:

---

Option Explicit
Option Compare Text


Sub GetTotal()
    Dim iRow As Long
    Dim Total As Double
    Dim TheYear As Variant, TheMonth As Variant
    Dim Theitem As String
 
   
    With ActiveSheet
        ' get your values from the linked cells.
        TheYear = Trim(.Range("E1").Value)
        TheMonth = Trim(.Range("F1").Value)
        Theitem = Trim(.Range("G1").Value)
       
        For iRow = 2 To .Cells(1, 1).CurrentRegion.Rows.Count
            If CStr(.Cells(iRow, 1).Value) = TheYear Or TheYear = "all" Then
                If .Cells(iRow, 2).Value = TheMonth Or TheMonth = "all" Then
                    If .Cells(iRow, 3).Value = Theitem Or Theitem = "all" Then
                        Total = Total + .Cells(iRow, 4).Value
                    End If
                End If
            End If
        Next iRow
       
        .Cells(1, 1).End(xlDown).Offset(1, 3).Value = Total  ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
   
    End With
End Sub

---

thanks again Paul yes this is what i'm looking for but it remains a problem when i run macro repeatedly it change total every time ,I would sum one time because i expect pressing Unintentionally and gives me wrong total after you if is possible ,it is summing total one time not change after this what ever i press the macro repeatedly :cool:

Can you please use punctiuation marks, capitals, etc. to facilitate any reader ?

Quote:

---

Originally Posted by maghari

thanks again Paul yes this is what i'm looking for but it remains a problem when i run macro repeatedly it change total every time ,I would sum one time because i expect pressing Unintentionally and gives me wrong total after you if is possible ,it is summing total one time not change after this what ever i press the macro repeatedly :cool:

---

If I understand, I'm afraid I don't see that

I can click the button multiple times and the total doesn't change

What EXACTLY are you doing if you see that


Attachment 27475

you're right Paul , actually I don't focus very good :doh: , My mistake when I select all in year
This changes continuously , Is there way to fix it please ?

I can't tell exactly what you're doing, but I added extra checks just in case


Attachment 27478

Code:

---

Option Explicit
Option Compare Text


Sub GetTotal()
    Dim iRow As Long
    Dim Total As Double
    Dim TheYear As String, TheMonth As String, Theitem As String
    Dim r As Range
 
   
    With ActiveSheet
        Set r = .Cells(1, 1)
        Set r = Range(r, r.End(xlDown).Offset(0, 3))
   
        ' get your values from the linked cells.
        TheYear = Trim(.Range("E1").Value)
        TheMonth = Trim(.Range("F1").Value)
        Theitem = Trim(.Range("G1").Value)
       
        For iRow = 2 To r.Rows.Count
            If .Cells(iRow, 1).Value <> TheYear And TheYear <> "all" Then GoTo NextLine
            If .Cells(iRow, 2).Value <> TheMonth And TheMonth <> "all" Then GoTo NextLine
            If .Cells(iRow, 3).Value <> Theitem And Theitem <> "all" Then GoTo NextLine
           
            Total = Total + .Cells(iRow, 4).Value
NextLine:
        Next iRow
       
        .Cells(1, 1).End(xlDown).Offset(1, 3).Value = Total
   
    End With
End Sub

---

Awesome updating ! Thanks for your assistance and your time
Now the code works completely :clap:
Best regards,
Maghari