Code to sum a range if codition meets

[Yeshu]

Hi,
I am tryin to sum up the range if the given coditions are met. But unfortunately it aint working properly. It is basically summing up the entire range.

Sub SysDown()
Dim Count As Long
Dim Rowend As Long
Dim Total As String
Count = 2
Rowend = 2
While Cells(Rowend, 3) <> ""
Rowend = Rowend + 1
Wend
Total = 0
While Count <= Rowend
If Cells(Count, 5) = Cells(65528, 3) And Cells(Count, 3) = "System Down" Then
Total = "=Sum(Q2:Q50000)"
End If
Count = Count + 1
Cells(65533, 9) = Total
Wend
End Sub

Can someone please HELP.

 

[Makrini]

maybe if you tell us what the conditions are...

(because by reading your code... Cell

Cells(65533, 9)

will either be =Sum(Q2:Q50000) or it will be nothing

 

[Yeshu]

Hi Again Makrini - Ya the condition are
1. Cells(Count, 5) = Cells(65528, 3) |*this is basically checking all the rows of col.7 to be equal to cell 65525(where I have marked a date of some previous time)*|

When this date matches, then it should check the another field of same row data i.e.,

2. Cells(Count, 3) = "System Down" |*this is basically checking all the rows of col.3 value to be equal to System Down*|

Hope I make sense??

Yeshu

maybe if you tell us what the conditions are...

(because by reading your code... Cell

Cells(65533, 9)

will either be =Sum(Q2:Q50000) or it will be nothing

 

[Makrini]

Not quite making sense to me yet..

Are you wanting to add all the values in column Q where the value in column E matches your date in C85528 and Column C is "System Down"?

Hi Again Makrini - Ya the condition are
1. Cells(Count, 5) = Cells(65528, 3) |*this is basically checking all the rows of col.7 to be equal to cell 65525(where I have marked a date of some previous time)*|

When this date matches, then it should check the another field of same row data i.e.,

2. Cells(Count, 3) = "System Down" |*this is basically checking all the rows of col.3 value to be equal to System Down*|

Hope I make sense??

Yeshu

 

[Peter_SSs]

Yeshu

Welcome to the MrExcel board!

Do you really need a macro to do this? Would this formula placed directly in cell I65533 work for you?

=SUMPRODUCT(--(E2:E50000=C65528),--(C2:C50000="System Down"),Q2:Q50000)

 

[Yeshu]

Exactly....

 

[Yeshu]

Awesome...it works...

Is it possible to add one more condition in it.

G2:G50000<>Mr X ????????

 

[Peter_SSs]

Awesome...it works...

Is it possible to add one more condition in it.

G2:G50000<>Mr X ????????

Sure! Did you try? You can see how I entered the original two conditions, so try just putting another one in there and see how you go. If it doesn't work, then post back with the formula that you tried.

 

[Yeshu]

Yes... I tried and to me surprise it works in first go.....

Awesome you guys are.....HATS OFF for you SIR!!!!!

 

[Yeshu]

Makrini - if you please can rectify the code..... then it will really help me in other coding.