Results 1 to 12 of 12
  1. #1
    Lounger
    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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?

  3. #3
    Platinum Lounger
    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 2-4, 5-24 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.
    Gre

  4. #4
    Bronze Lounger
    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>

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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 (ctrl-shift-enter) 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

  6. #6
    Lounger
    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.

  7. #7
    Lounger
    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.

  8. #8
    Lounger
    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.

  9. #9
    Lounger
    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!

  10. #10
    Lounger
    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.

  11. #11
    WS Lounge VIP sdckapr's Avatar
    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>


  12. #12
    Lounger
    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.

Posting Permissions

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