+ Reply to Thread
Results 1 to 3 of 3

Combining two COUNTIFS formulas

  1. #1
    Forum Contributor
    Join Date
    07-10-2012
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    387

    Combining two COUNTIFS formulas

    Hello,

    I need assistance adding this COUNTIFS:

    =IF(COUNTIFS('Data Entry'!$B$3:$B$100000,$A5,'Data Entry'!$E$3:$E$100000,$D$1),"PERFECT USED")

    Before the first COUNTIFS in the below:

    =IF(B5>$D$2,"X",IF(COUNTIFS('Data Entry'!$B$3:$B$1048576,A5,'Data Entry'!$A$3:$A$1048576,">="&$D$2,'Data Entry'!$A$3:$A$1048576,"<="&$D$3)=0,"PERFECT ATTENDANCE",COUNTIFS('Data Entry'!$B$3:$B$1048576,A5,'Data Entry'!$A$3:$A$1048576,">="&$D$2,'Data Entry'!$A$3:$A$1048576,"<="&$D$3)))

    I am trying to insert it here, but I keep getting errors:

    =IF(B5>$D$2,"X",---Inserting Here---IF(COUNTIFS('Data Entry'!$B$3:$B$1048576,A5,'Data Entry'!$A$3:$A$1048576,">="&$D$2,'Data Entry'!$A$3:$A$1048576,"<="&$D$3)=0,"PERFECT ATTENDANCE",COUNTIFS('Data Entry'!$B$3:$B$1048576,A5,'Data Entry'!$A$3:$A$1048576,">="&$D$2,'Data Entry'!$A$3:$A$1048576,"<="&$D$3)))

    I appreciate any help.

    Thank you,
    Nick

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,530

    Re: Combining two COUNTIFS formulas

    Without knowing what you want this formula to do, or what your data looks like, I can only guess. But this is a valid formula, with line breaks to show the logic structure:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Here is the same formula on one line:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Notice that your final FALSE case just show a number as the result of COUNTIFS, which is what your original formula does. Not sure if that's what you wanted because all the other cases return a string.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Contributor
    Join Date
    07-10-2012
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    387

    Re: Combining two COUNTIFS formulas

    Thanks 6StringJazzer, I kept looking at it, on the version I was attempting I had a ) after Perfect Used, messing it all up.

+ 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. Need help combining Countifs with sumproduct
    By kartikoli in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-09-2019, 10:09 AM
  2. Help with combining COUNTIFS and SUM FREQUENCY IF formulas
    By Bergentrude1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-05-2018, 03:46 PM
  3. [SOLVED] Combining COUNTIFS with VLOOKUP?
    By jeptik in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 06-08-2018, 01:59 PM
  4. Combining Indirect with Countifs
    By arthurphil in forum Excel General
    Replies: 7
    Last Post: 01-27-2015, 08:13 AM
  5. [SOLVED] Combining CountIfS Formulas
    By Phil Hageman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-21-2013, 09:25 AM
  6. Combining COUNTIFS and OR function
    By tatyanamarie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-12-2013, 08:51 AM
  7. [SOLVED] Combining two COUNTIFS
    By fletch8701 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-19-2012, 11:07 PM

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