Need to add values based on three other criteria

[Jonathan]

Hi,

I need help with my code, its only doing something simple but I've
tried select case statements and if...then statements. I'm very much a
newbie so....

(Xn) means Column X, Row n

I want to add currency values in a column, (Gn) based on the criteria
in three other columns, i.e.

Add the value of (Gn) to a variable when (An) = 4 AND (Cn) = Test AND
(Hn) = Parts.

I've tried some simple code that defines the necessary variables, I've
got a command button on the sheet where the data is and the code is in
their too, all it seems to do at the moment is assign the
initialisation value to the variable and then put this in the cell at
the end, it just seems to ignore the logic in the IF loop, although if
I get it to print out a msgbox for each of the variables it loops
through the entire range Im looking at. I don't get any errors with
the logic, it just doesn't perform the action I expect.

The actual code (after the dim statements and variable initialisation
is

For i = 10 To 260

mnth = Cells(i, 1).Value
prj = Cells(i, 3).Value
cat = Cells(i, 8).Value
subtot = Cells(i, 7).Value

If (Cells(i, 1).Value = 4 And Cells(i, 3).Value = "Test" And
Cells(i, 8).Value = "Consumables") Then AstCon3 = AstCon3 + subtot

I've also tried it this way...

If mnth = "4" And prj = "Test" And cat = "Consumables" Then
AstCon3 = AstCon3 + subtot

End If

Next i

Range("AB1002") = AstCon3

Can anyone give me some pointers please

J

 

[Carim]

Hi,

take a look at the function Sumproduct() ...
it will easily fix your problem ...

HTH
Cheers
Carim

 

[Jonathan]

Hi Carim,

The sumproduct() function doesnt do what I need,

What I'm basically after is summing various values from cells in
column G based on the criteria of
value in same row from column A AND Column C AND Column H so for
example

when A1 is 4 AND C1 is Test AND H1 is Consumables the value of G1 is
added to a variable.

I then need to loop through to the next row and perform the same
check, if the same criteria are matched then I need to add the value
from G2 to the variable.

When I have looped through the rows I can then put the total (that
might be something along the lines of the sum of (G1, G2, G4, G100)
into a cell on the spreadsheet so that I can use it elsewhere. I seem
to be able to manage everything apart from actually matching the
criteria.

Regards

J