Results 1 to 4 of 4

Thread: COUNTIF statement
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2015
    Posts
    193
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default COUNTIF statement

    Hello,

    Looking to do a COUNTIF statement I believe.

    We are looking to find a particular salesperson in this instance Caitlin which is located in column B and then looking for STATE in column E if this is TRUE then total the values found in column H against all the lines found.

    The formula will always be kept on Sheet "Total" in Cell "A4"

    Example below:

    Date Sales Person Quote Number Cust name State Post code Shed Size Quote Value
    1 Caitlin NSW $1000
    1 Caitlin QLD $9999
    1 Caitlin NSW $2000
    1 Caitlin NSW $3000

    Probably an easy one for you guru's appreciate the help

    Luke

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: COUNTIF statement

    Something like this?

    Excel 2016
    ABCDEFGH
    1DateSales PersonQuote NumberCust nameStatePost codeShed SizeQuote Value
    21CaitlinNSW$1,000
    31CaitlinQLD$9,999
    41CaitlinNSW$2,000
    51CaitlinNSW$3,000

    Data






    Excel 2016
    AB
    1Sales PersonCaitlin
    2StateNSW
    3
    4Total6000

    Total



    Worksheet Formulas
    CellFormula
    B4=SUMIFS(Data!H2:H10,Data!B2:B10,Data!L1,Data!E2:E10,Data!L2)

    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    Board Regular
    Join Date
    Nov 2015
    Posts
    193
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIF statement

    Quote Originally Posted by Peter_SSs View Post
    Something like this?

    Excel 2016
    A B C D E F G H
    1 Date Sales Person Quote Number Cust name State Post code Shed Size Quote Value
    2 1 Caitlin NSW $1,000
    3 1 Caitlin QLD $9,999
    4 1 Caitlin NSW $2,000
    5 1 Caitlin NSW $3,000
    Data




    Excel 2016
    A B
    1 Sales Person Caitlin
    2 State NSW
    3
    4 Total 6000
    Total

    Worksheet Formulas
    Cell Formula
    B4 =SUMIFS(Data!H2:H10,Data!B2:B10,Data!L1,Data!E2:E10,Data!L2)
    Thanks for helping! unfortunately returning a result with 0.

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: COUNTIF statement

    Quote Originally Posted by Lukums View Post
    Thanks for helping! unfortunately returning a result with 0.
    Are the values in column H actual numbers, or text?
    What happens if you put this in a blank cell on the data sheet? =ISNUMBER(H2)
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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