none
Excel VBA countif column A contains and column B value is greater

    Question

  • Hi guys,

    I am looking to have a macro that allows me to scan 2 columns and count the time that someone got below a certain value.  The columns look like this:

    Column A      Column B

    John                 7
    John                 5
    John                 6
    Marie                8
    Marie                9
    Marie                9

    Basically, i need to find how many times does John score below 6 points.  I've tried with Countif or SUMPRODUCT.  It doesn't wor, therefore, I do need a VBA function.  Please help.

    Thank you very much.

    Thursday, February 09, 2012 3:38 AM

Answers

  • Sumproduct works for me :)

    =SUMPRODUCT((A1:A6="John")*(B1:B6<6))

    Few Points

    1) CountIfs() won't work if you have Excel 2003 or below.

    2) Also SUMPRODUCT and CountIfs() wont work if the numbers are actually not numbers but are "Stored as text". Please ensure that the macro stores them as numbers.


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.


    Thursday, February 09, 2012 2:04 PM

All replies

  • Hi,

    I think the function that you may want to try is CountIFS.  this funtion lets you set multiple sets of criteria.

    Here is a link to the help file for this function:  http://office.microsoft.com/en-us/excel-help/countifs-function-HA010047494.aspx

    It has some helpful examples.

    Please let me know if this helped.

    Mike Corkery, MCT, MCPD, MCITP, etc.

    Thursday, February 09, 2012 3:46 AM
  • Try this

    =COUNTIFS(A1:A6,"John",B1:B6,"<6")

    Thursday, February 09, 2012 3:48 AM
  • Try this

    =COUNTIFS(A1:A6,"John",B1:B6,"<6")

    I think the Landlord should copy this code because it's easy to understand. By the way, you can modify the ranges:

    A1:A6 & B1:B6 

    to

    A:A  & B:B

    Hope it helps.


    • Edited by longnosee Thursday, February 09, 2012 6:20 AM
    Thursday, February 09, 2012 6:20 AM
  • Try this

    =COUNTIFS(A1:A6,"John",B1:B6,"<6")

    I think the Landlord should copy this code because it's easy to understand. By the way, you can modify the ranges:

    A1:A6 & B1:B6 

    to

    A:A  & B:B

    Hope it helps.



    By the way its not a code its a formula that goes into a cell.
    Thursday, February 09, 2012 12:39 PM
  • i've used the countif() function, it does not work because my data / columns have been formatted by a macro.  Somehow, countif does not work....
    Thursday, February 09, 2012 1:42 PM
  • Are you using Countif() or Countifs() with an s
    • Edited by Mike7952 Thursday, February 09, 2012 1:51 PM
    Thursday, February 09, 2012 1:48 PM
  • Sumproduct works for me :)

    =SUMPRODUCT((A1:A6="John")*(B1:B6<6))

    Few Points

    1) CountIfs() won't work if you have Excel 2003 or below.

    2) Also SUMPRODUCT and CountIfs() wont work if the numbers are actually not numbers but are "Stored as text". Please ensure that the macro stores them as numbers.


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.


    Thursday, February 09, 2012 2:04 PM