Sums and CountIfs

  • There is this huge file I have and I want to count the items in column A for each location (identified by a number in the reference sheet). Why aren't my sums working (in the first two of column B) or do I have to use CountIfs? If so, how would I phrase the countif with two conditions?


    Thank you for your help,


    HarriBess

    Files

  • Re: Sums and CountIfs


    Please make sure there are no external references. In addition there are circular references you may want to correct first.

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: Sums and CountIfs


    Quote from Wigi

    Please make sure there are no external references. In addition there are circular references you may want to correct first.


    When I opened your file, there were links to other sheets on your PC. Obviously, we here don't have these files so we cannot see what is happening.


    Circular references mean that some cell x refers to another cell y AND y refers to x. Excel cannot calculate the result.


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: Sums and CountIfs


    Okay. I think I have my workbook page set up for easier viewing. I just pasted the values.


    What I want to do is look at data on another sheet and summarize by counting the number of occurances of that Rotor/Stator for the Location, which are all assigned numbers on that some sheet.


    Is this better?


    I just can't figure out which formula to use nor how to phrase it.

  • Re: Sums and CountIfs


    Harri


    see attachment for an example of the SUMPRODUCT function. That will do what you want. Adapt to your setting.


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: Sums and CountIfs


    Okay. So I tried the SUMPRODUCT, it's giving me zeros when I can look at the data and count more than that. Here's what I've typed:


    =SUMPRODUCT(--(Sheet1!F2:F18620=Summary!B3&"*")*--(Sheet1!G2:G18620=101004))


    How am I continuing to do this wrong??

  • Re: Sums and CountIfs


    The only thing I can think of that's causing the 0, could be the *


    Are you really looking for the cell value of B3 and immediately after that value an asterisk *?


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: Sums and CountIfs


    Quote from Wigi

    The only thing I can think of that's causing the 0, could be the *


    Are you really looking for the cell value of B3 and immediately after that value an asterisk *?


    Wigi


    What causes my method to not work? In the file I attached it worked.
    Kris?


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: Sums and CountIfs


    Nope. Not working for me either.
    Now my supervisor's goal is a Macro; in case you couldn't tell, I suck at those.


    This is what my supervisor sent me, but it tells me there's a bug:


    Sheets("CODTL").Select
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveWindow.ScrollColumn = 1
    Selection.AutoFilter Field:=1, Criteria1:="=P2*", Operator:=xlAnd
    Selection.AutoFilter Field:=2, Criteria1:="101004"
    Selection.AutoFilter Field:=9, Criteria1:="=STATOR K59*", Operator:=xlAnd
    ActiveWindow.SmallScroll ToRight:=3
    Range("L3066").Select
    Selection.AutoFilter Field:=12, Criteria1:=">=2/1/2006", Operator:=xlAnd _
    , Criteria2:="<=2/17/2006"
    ActiveWindow.SmallScroll ToRight:=33
    Range("A1:AR18820").Select
    Range("AR1").Activate
    Selection.Copy
    Sheets("Hartselle").Select
    ActiveWindow.ScrollRow = 1
    Range("A1").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=207
    Range("B228").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = ""
    Range("B228").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(R[-226]C:R[-2]C,R[-226]C)"


    End Sub


    This code tells me that he wants all the items in field 1 that begin with P2. We also need S2. This code creates another page. How would I do this without the bug (it says it's in the row with the # 101004)?

  • Re: Sums and CountIfs


    Quote from Wigi

    What causes my method to not work? In the file I attached it worked.
    Kris?


    Wigi


    Actually my reply was against this


    =SUMPRODUCT(--(Sheet1!F2:F18620=Summary!B3&"*")*--(Sheet1!G2:G18620=101004))


    SUMPRODUCT won't accept wild cards.


    HTH