Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    I need to count number of occurences Col A has a "R" and col B has "VI"
    I tried =SUM((A1:A5="R")*(B1:B5="VI")) but get 1, even though there are 3 occurences. If I click the fx symbol to the left of the formula bar the drop down gives a value of 3. It seems to only show the value on that row, not the total.

    Len

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You actually need SUMPRODUCT:

    =SUMPRODUCT((A1:A5="R")*(B1:B5="VI"))
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Can you use a "count" or "countif" formula?

    Len

  4. #4
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Will this work for you?

    =COUNTIF(A1:A5,"r")+COUNTIF(B1:B5,"vi")

    Please note it is not case sensitive.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Tried that but it does not count occurences where both arguments occur on same row, but rather the # of occurences where either exists. It just gives a sum of all occurences regardless of "matches". See sample. I want to see how many times an "R" and VI are on same row.
    Attached Files Attached Files

  6. #6
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts
    Len, In column C add the following formula =IF(AND(A1="r",B1="vi"),1,0) copy it down as many rows as needed, then sum column C.

    Regards,
    Maria
    Maria
    Simmo7
    Victoria, Australia

  7. #7
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hi Len - Thanks for supplying the sample spreadsheet. That always helps to get responses.

    There are many possibilities to get what you want. I have attached an example. Col C can be hidden if necessary or Col C formulas can be calculated elsewhere on your worksheet.

    Like previous solutions, this only works when the "R" is in Col A and the "VI" is in Col B. Do you ever have "VI" in Col A and the "R" in col B?

    Continue to let us know if something different is needed.

    Tim
    Attached Files Attached Files

  8. #8
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Thanks Maria, but that would not be efficient with many entries if I had to do each line. I have only one variation in "R" column [it is present or absent], but 10 variations in the "VI" column and about 300 entries to calculate. I need to calculate individually how many [total/sum] of each of these variations have a "R" associated on same row.
    Example of desired results in about 300 entries:
    There are 10 "VI" with an "R"
    There are 7 "DD" with an "R"
    There are 2 "BS" with an "R"
    There are 12 "4T" with an "R"
    There are 0 "2T" with an "R"
    There are 8"Mn" with an "R"
    etc

    Intuitively, this seems that this should be a "count" function, but strangely it does not appear to be.

  9. #9
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    thanks everyone. I do not think I was clear nor was my samle xls. Attached is a better sample. I need to know how many [ individually ] VI, DD, etc have an "R" associated with that entry.

    VI=4 [with "R"]
    DD=5
    bs=1
    4t=5
    etc

  10. #10
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    forgot attachment
    Attached Files Attached Files

  11. #11
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hi Len - The examples keep getting clearer. Thanks for attaching that file.

    Does this new file (attached) give answers closer to what is desired? This solution uses an Excel Pivot Table.

    The data can be as many rows as needed and as many different variations in col A or Col B as needed.

    The view can be all of Col A or just "R" (or any other variable) in col A or just "blanks" in col A.
    The view can also be Just Col A with an "R" and "VI" (or any other variation) in col B

    There is no VB/Macro coding using this Pivot Table. Pivot Table is a very useful Excel feature.

    Tim
    Attached Files Attached Files

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Did you try the SUMPRODUCT at all?
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello - The attached file shows 2 possible solutions. They are shown on separate tabs (Pivot Table and SUMPRODUCT), although they could be displayed with the original data.

    Tim
    Attached Files Attached Files

  14. #14
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Rory,
    Yes, it works and I thank you for your input and assistance, but I am trying to become more proficient with Excel and and my project seems such a basic function for Excel that I assumed more approaches exist . The "countif" or "sumif" functions seem so intuitive here that I assumed one or both would be a valid and "neat" approach. I have learned "countif" does not work with more than 1 argument, but "sumif" I am still puzzled.

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Your original formula should work if you array enter it, but neither sumif nor countif accept multiple criteria. If you have 2007 or 2010 you can use COUNTIFS though.
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

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