Results 1 to 12 of 12
Thread: SUMIF COUNTIF(2000/2002)

20030724, 14:01 #1
 Join Date
 Dec 2001
 Posts
 48
 Thanks
 0
 Thanked 0 Times in 0 Posts
SUMIF COUNTIF(2000/2002)
Dear Helpers,
Need VBA code to get the OUTPUT as outlined in attached sample file. Also appreciate a similar code to COUNTIF the rows that satisfy the conditions. Many thanks in advance.

20030724, 14:09 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: SUMIF COUNTIF(2000/2002)
Would you like the VBA code to fill (for example) range G2:G28 with SUMIF formulas, or with the outcome of these formulas as constant values?

20030724, 14:17 #3
 Join Date
 Nov 2001
 Location
 Vienna, Wien, Austria
 Posts
 5,009
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUMIF COUNTIF(2000/2002)
It is not wholly clear why you are looking for VBA code rather than just formulas. Perhaps you could explain.
Also, the parameters change (slightly): from testing for Row 2, to testing for Row 5 and then for Row 25. Is there a condition which changes here, or is it always Rows 24, 524 and 25 onwards? If you are expecting to use code, whatever the nature of this condition is will have to be built into the code.Grüße

20030724, 14:26 #4
 Join Date
 Jun 2001
 Location
 New York, New York, Lebanon
 Posts
 1,449
 Thanks
 1
 Thanked 1 Time in 1 Post
Re: SUMIF COUNTIF(2000/2002)
<img src=/S/hello.gif border=0 alt=hello width=25 height=29> Selva
OK when you say <<< Need VBA code to get the OUTPUT as outlined in attached sample file. >>> what kind of output are you looking for? What format do you need the output to be in, and where do you want it to go? Some details is needed here.
Also if you are talking about output meaning calculation, why use VBA when you have a perfectly optimized worksheet function that does the same job, only better.
The code for the SumIf and CountIf are the same, except CountIf i used in place of SumIf.
Hope this helps
Wassim
PS The cells I changed are in Red Bold Font, and are in K1 to M1. Also make sure that what you have in B2, DH6, looks like a cell address, so you may have trouble if you don't use quotes around it.<img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

20030724, 15:01 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: SUMIF COUNTIF(2000/2002)
I used ARRAY formulas (ctrlshiftenter) instead of a macro. I got different numbers than some of your example, but looking at them, am unsure where some of your numbers came from.
In G1:
=SUM(IF($B$2:$B$28=$B2,$E$2:$E$28))
In H1
=SUM(IF(($B$2:$B$28=$B2)*($C$2:$C$28=$C2),$E$2:$E$ 28))
In I1:
=SUM(IF(($B$2:$B$28=$B2)*($C$2:$C$28=$C2)*(TEXT($A $2:$A$28,"mm/yy")=$J2),$E$2:$E$28))
Copy these values to G2:I28
Steve

20030725, 03:23 #6
 Join Date
 Dec 2001
 Posts
 48
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUMIF COUNTIF(2000/2002)
Hello HansV,
I need the outcomes of the fomulas as you so correctly put it. Sorry I did not think of this ambiguity in my request.

20030725, 03:28 #7
 Join Date
 Dec 2001
 Posts
 48
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUMIF COUNTIF(2000/2002)
Hello unkamunka,
No changes in conditions. Looking for VBA to make code flexible for wider use. Formulas will also do as Steve has given. Basically it is to sum values when the columns meet the conditions. The rows range is always ALL the rows in the Sheet. Sorry for ambiguity.

20030725, 03:40 #8
 Join Date
 Dec 2001
 Posts
 48
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUMIF COUNTIF(2000/2002)
Hello Steve,
I got your G1 formula working fine. Got problem with H1 formula. You use *. Does this equate to 'multiply'? IF YES, that is not the intent. Intent is 'AND'. How do you put in the AND logic into the formula? There is a possibility * does the AND job. But the result does not show correct value. Tried this but no joy:
=SUM(IF(AND($B$2:$B$28=$B2,$C$2:$C$28=$C2),$E$2:$E $28)). Appreciate your effort.

20030725, 03:50 #9
 Join Date
 Dec 2001
 Posts
 48
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUMIF COUNTIF(2000/2002)
Steve, Please accept my apologies. I have the answers. * does mean AND. Your formulas work just great. Thanks a 10^6!

20030725, 07:04 #10
 Join Date
 Dec 2001
 Posts
 48
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUMIF COUNTIF(2000/2002)
Steve, The SSheet of mine has 2000 rows. Pasting the formula is sapping the memory. VBA to just paste the VALUES from the superb formulas please. Perhaps HansV can also help. Thanks gentlemen.

20030725, 10:23 #11
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: SUMIF COUNTIF(2000/2002)
Arrays can be be intensive. Try this. Change as appropriate.
Steve
<pre>Option Explicit
Sub SelvaCalc()
Dim wks As Worksheet
Dim lRow As Long
Dim x As Long
Dim y As Long
Dim i As Integer
Dim dT(1 To 3) As Double
Application.ScreenUpdating = False
Set wks = Worksheets("sheet1")
lRow = wks.Range("a65536").End(xlUp).Row
With wks
For x = 2 To lRow
For i = 1 To 3
dT(i) = 0
Next i
For y = 2 To lRow
If .Cells(y, 2).Value = .Cells(x, 2).Value Then
dT(1) = dT(1) + .Cells(y, 5)
If .Cells(y, 3).Value = .Cells(x, 3).Value Then
dT(2) = dT(2) + .Cells(y, 5)
If Format(.Cells(y, 1).Value, "mm/yy") = _
.Cells(x, 10).Value Then
dT(3) = dT(3) + .Cells(y, 5)
End If
End If
End If
Next y
For i = 1 To 3
.Cells(x, 6 + i).Value = dT(i)
Next i
Next x
End With
Application.ScreenUpdating = True
End Sub</pre>

20030725, 14:08 #12
 Join Date
 Dec 2001
 Posts
 48
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUMIF COUNTIF(2000/2002)
Steve,
As always, excellent code. Works like clockwork! Truly grateful.