+ Reply to Thread
Results 1 to 6 of 6

COUNTIFS Problem has got me baffled...Please Help!

  1. #1
    Registered User
    Join Date
    12-20-2013
    Location
    Fort Worth, Texas
    MS-Off Ver
    Excel 2013
    Posts
    5

    Question COUNTIFS Problem has got me baffled...Please Help!

    Can someone please tell my why my formula works like:

    =COUNTIFS('Job Entry Form'!A:A,G6,'Job Entry Form'!K:K,">="&I6,'Job Entry Form'!K:K,"<="&J6,K:K,">=4",K:K,"<=7",'Job Entry Form'!J:J,"Yes")

    but not like:

    =COUNTIFS('Job Entry Form'!A:A,G6,'Job Entry Form'!K:K,">="&I6,'Job Entry Form'!K:K,"<="&J6,K6,">=4",K6,"<=7",'Job Entry Form'!J:J,"Yes")

    I am trying to get it to check a particular cell (K6) instead of the whole (K:K) column. I know this has to be possible, any help on what I'm doing wrong?

    It's on the "Running Log" page. I am trying to help my wife build a tool to help her keep track of her salesman's commissions and bonuses. It works for pulling the info she needs on a weekly basis, but I want to make a running total page for he as well, so I can make a reconcile page.

    Thanks in advance,
    David
    Attached Files Attached Files
    Last edited by ogbugsy; 12-23-2013 at 11:02 AM.

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: COUNTIFS Problem has got me baffled...Please Help!

    It doesn't work because sumifs needs all the ranges it evaluates to be the same size.
    You'll have to do the single cell evaluation seperately.

    Try

    =IF(AND(K6>=4,K6<=7),COUNTIFS('Job Entry Form'!A:A,G6,'Job Entry Form'!K:K,">="&I6,'Job Entry Form'!K:K,"<="&J6,'Job Entry Form'!J:J,"Yes"),0)

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: COUNTIFS Problem has got me baffled...Please Help!

    Little bit not clear about where exactly you would like to compare the data

    'Job Entry Form'!A:A - Refers to the column-A of Job Entry Form
    A:A - Refers to the current sheet on which the formula is applied


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  4. #4
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    Sunderland, England
    MS-Off Ver
    Excel 2007 (Home) / 2003 (Work)
    Posts
    740

    Re: COUNTIFS Problem has got me baffled...Please Help!

    What is being referred to in column E? I'm baffled myself.

  5. #5
    Registered User
    Join Date
    12-20-2013
    Location
    Fort Worth, Texas
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: COUNTIFS Problem has got me baffled...Please Help!

    That's what I was looking for tyvm.

    Is there a way to get it to = a "1" instead of adding them up? with this:

    =COUNTIFS('Job Entry Form'!A:A,G6,'Job Entry Form'!K:K,">="&I6,'Job Entry Form'!K:K,"<="&J6,K:K,">=4",K:K,"<=7",'Job Entry Form'!J:J,"Yes")

    I was getting a "1" not a count.

    I have it looking at K6 to see if the # is: K6>=4,K6<=7 or K6>=8,K6<=88 or K6>=12

    If the salesman sells from 4-7 they get one bonus, if 8-11 another, and 12+ another, so I need it to give me a 1, not a count. The formula I started with "above" returns a 1, but reads the whole column.

    Thanks,
    David
    Last edited by ogbugsy; 12-23-2013 at 10:52 AM.

  6. #6
    Registered User
    Join Date
    12-20-2013
    Location
    Fort Worth, Texas
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: COUNTIFS Problem has got me baffled...Please Help!

    Column E is where I am calculating how many contracts the salesman has for each week that are eligible for a quantity bonus.

    =COUNTIFS('Job Entry Form'!A:A,'Running Log'!A6,'Job Entry Form'!J:J,"yes",'Job Entry Form'!K:K,">="&C6,'Job Entry Form'!K:K,"<="&D6)

    -salesman name
    -wether a scope check has been picked up
    -date range

    Thanks,
    David
    Last edited by ogbugsy; 12-23-2013 at 10:50 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Baffled by SumIf issue.
    By MLocke in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-02-2013, 03:31 PM
  2. [SOLVED] Excel 2007 : Baffled by the (lack of) result of a countif
    By neil40 in forum Excel General
    Replies: 1
    Last Post: 04-01-2012, 06:43 AM
  3. Strange Vlookup result - baffled
    By andycaps in forum Excel General
    Replies: 9
    Last Post: 01-18-2012, 08:58 AM
  4. Calculation in cell not updating - Totally baffled!
    By bmunoz64 in forum Excel General
    Replies: 1
    Last Post: 01-11-2012, 12:08 PM
  5. Replies: 0
    Last Post: 12-16-2011, 09:01 AM

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