+ Reply to Thread
Results 1 to 15 of 15

Countif with a multiply critera to collumns value

  1. #1
    Registered User
    Join Date
    10-08-2015
    Location
    Gothenburg
    MS-Off Ver
    2016
    Posts
    71

    Countif with a multiply critera to collumns value

    I just made up this sample book to see if we together could solve this easy puzzle.

    I got 2 different collums (Count and Size). I'm using Countif to look for my value, and it does find them and show how many of that size there is, but I also want it to depend on the count value, and multiply the certain size with the specific count.

    For example
    See attached workbook

    sample book.xlsx

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Excel 2010 2016
    Posts
    2,949

    Re: Countif with a multiply critera to collumns value

    My first thought is that you will probably have to resort to VBA (macro)
    I'll see if I can come up with a formula but those are not my strong
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Registered User
    Join Date
    10-08-2015
    Location
    Gothenburg
    MS-Off Ver
    2016
    Posts
    71

    Re: Countif with a multiply critera to collumns value

    Quote Originally Posted by Keebellah View Post
    My first thought is that you will probably have to resort to VBA (macro)
    I'll see if I can come up with a formula but those are not my strong
    I could use a macro (VBA) as well, doesn't have to be a formula. Thanks Keebellah.

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Excel 2010 2016
    Posts
    2,949

    Re: Countif with a multiply critera to collumns value

    With a macro it's quite simple
    Do you know how to write a macro?
    You use the same range and check for the size value and the just add the number in the offset (0,-1) as count.

  5. #5
    Registered User
    Join Date
    10-08-2015
    Location
    Gothenburg
    MS-Off Ver
    2016
    Posts
    71

    Re: Countif with a multiply critera to collumns value

    I'm not to familiar how to write the vba macros, I'm just good at understanding them once they are written xD

    So if you could give me a hand I'd appreciate it alot.

  6. #6
    Forum Guru
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    XP - 2007 & Win10 - Office 365
    Posts
    8,094

    Re: Countif with a multiply critera to collumns value

    If you are still interested in a formula solution try this in C32 and fill down.

    (I come up with a different value for Count 660 ... 33)
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In order to get the same counts you expect I used this formula.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Edit Please see my next post.
    Last edited by FlameRetired; 07-11-2018 at 05:16 PM. Reason: After thought
    Dave

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Excel 2010 2016
    Posts
    2,949

    Re: Countif with a multiply critera to collumns value

    The formula works great

    I wrote the following function

    Please Login or Register  to view this content.
    It's also in the attached file as well as the formula
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    XP - 2007 & Win10 - Office 365
    Posts
    8,094

    Re: Countif with a multiply critera to collumns value

    Pfft! I way over complicated it. Try this instead.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  9. #9
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Excel 2010 2016
    Posts
    2,949

    Re: Countif with a multiply critera to collumns value

    @FlareRetired: your formula is correct but the OP wants to add the values next to the size values so if there is a 1 to the left of 660 it counts one is, if there is a 2 then that one counts for 2 etc.
    The vba Function I wrote takes that into consideration.

  10. #10
    Forum Guru
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    XP - 2007 & Win10 - Office 365
    Posts
    8,094

    Re: Countif with a multiply critera to collumns value

    Yes and so did my first formula, but the results in the upload suggest they want a count of the counts ... ie 1 counts as 1 and so does 2. We've both figured sum of counts which is how I interpreted the instructions at first.

    Try this in your workbook in C32.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Here they are:


    A
    B
    C
    32
    Count 190
    66
    66
    33
    Count 370
    18
    18
    34
    Count 660
    33
    33


    What OP's upload shows is:


    A
    B
    32
    Count 190
    65
    33
    Count 370
    18
    34
    Count 660
    28



    I think we need feedback from OP.
    Last edited by FlameRetired; 07-11-2018 at 07:20 PM.

  11. #11
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Excel 2010 2016
    Posts
    2,949

    Re: Countif with a multiply critera to collumns value

    True, the OP is the only one to clarify this, but the OP first upload was that he knew how to count the size occurrences and that he/she was looking for a way to add the values next to the sizes, so I think ....

    Well, we wait
    We did our best

  12. #12
    Registered User
    Join Date
    10-08-2015
    Location
    Gothenburg
    MS-Off Ver
    2016
    Posts
    71

    Re: Countif with a multiply critera to collumns value

    I want to thank you both for the effort you put into my question. Both of the formulas/functions works great, So I'll rep you both!
    As you describe my case it's accurate. My formula only counted the value of the 660 for ex. and didn't calculate the ammount of them, but yours does!

    Thanks again, this will help me alot in the future.

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    17,630

    Re: Countif with a multiply critera to collumns value

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on their reputation star bottom left.

  14. #14
    Registered User
    Join Date
    10-08-2015
    Location
    Gothenburg
    MS-Off Ver
    2016
    Posts
    71

    Re: Countif with a multiply critera to collumns value

    Ah, forgot. Marked it as solved now
    Last edited by AliGW; 07-12-2018 at 06:01 AM.

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    17,630

    Re: Countif with a multiply critera to collumns value

    Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1