+ Reply to Thread
Results 1 to 13 of 13

Trying to use the COUNTIFS formula, but might need SUMPRODUCT instead?

  1. 02-18-2013, 04:21 PM #1
    jeepjenn
    jeepjenn is offline
    Registered User
    Join Date
    07-20-2012
    Location
    Ottawa Canada
    MS-Off Ver
    Excel 2003
    Posts
    20

    Trying to use the COUNTIFS formula, but might need SUMPRODUCT instead?

    I have a spreadsheet that spans column A to Column N

    Column B contains Package Names (that can contain numerous forms)

    Column D-M contain dates for which the form has moved from one group to another for approval.

    I need to figure out a way to check the following:

    If column E, G, H, I, J and K are all filled (with dates) and column L is empty (no date), I need the formula to look at Column B and count the Package Names only once (there can be duplicate names since there can be many forms in the same Package)


    Any Ideas??

    (I am using Excel 2007)
    Reply With QuoteRegister To Reply
  2. 02-18-2013, 04:26 PM #2
    Ace_XL
    Ace_XL is offline
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Trying to use the COUNTIFS formula, but might need SUMPRODUCT instead?

    Maybe

    Please Login or Register  to view this content.
    Life's a spreadsheet, Excel!
    Say thanks, Click *
    Reply With QuoteRegister To Reply
  3. 02-18-2013, 04:57 PM #3
    jeepjenn
    jeepjenn is offline
    Registered User
    Join Date
    07-20-2012
    Location
    Ottawa Canada
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Trying to use the COUNTIFS formula, but might need SUMPRODUCT instead?

    Ok, I understand the formula from E:E on, but how is the B:B,B2 part going to count the number of unique Package Names? Currently my result is 0 which is incorrect.

    Am I missing something?
    Reply With QuoteRegister To Reply
  4. 02-19-2013, 04:58 PM #4
    Haseeb Avarakkan
    Haseeb Avarakkan is offline
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Trying to use the COUNTIFS formula, but might need SUMPRODUCT instead?

    Try this Array Formula with CTRL+SHIFT+ENTER,

    =SUM(IF(FREQUENCY(IF(ISNUMBER(E2:E10),IF(L2:L10="",IF(MMULT(ISNUMBER(G2:K10)+0,{1;1;1;1;1})=5,IFERROR(MATCH(B2:B10,B2:B10,0),"")))),ROW(B2:B10)-ROW(B2)+1),1))

    Adjust the ranges.
    Regards,
    Haseeb Avarakkan
    __________________________________
    "Feedback is the breakfast of champions"
    Reply With QuoteRegister To Reply
  5. 02-20-2013, 10:48 AM #5
    jeepjenn
    jeepjenn is offline
    Registered User
    Join Date
    07-20-2012
    Location
    Ottawa Canada
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Trying to use the COUNTIFS formula, but might need SUMPRODUCT instead?

    Hi Haseeb,

    Can you give me a quick explaination of this formula? That would help me figure out which ranges I need to change to make it work.

    Thank You,

    Jennifer
    Reply With QuoteRegister To Reply
  6. 02-20-2013, 08:45 PM #6
    Haseeb Avarakkan
    Haseeb Avarakkan is offline
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Trying to use the COUNTIFS formula, but might need SUMPRODUCT instead?

    Jennifer, I am not god in to explain something. let me try my level best.

    This was your requrements "If column E, G, H, I, J and K are all filled (with dates) and column L is empty (no date), I need the formula to look at Column B and count the Package Names only once"

    So first condition column E have dates, I used here range as row 2:10

    IF(ISNUMBER(E2:E10)

    2nd condition "Column L is empty"

    IF(L2:L10=""

    3rd condition, "G, H, I, J and K are all filled (with dates)"

    IF(MMULT(ISNUMBER(G2:K10)+0,{1;1;1;1;1})=5 This will check in each row have filled or not, if all rows are filled will give array result of 5

    If all these 3 conditions are met, take MATCH results from the array. If any of the cell in B2:B10 is empty MATCH will give error, so used IFERROR to avoid this issue.

    IFERROR(MATCH(B2:B10,B2:B10,0),"")

    Change Row 2:10 with your ranges in each columns.
    Reply With QuoteRegister To Reply
  7. 02-21-2013, 11:47 AM #7
    jeepjenn
    jeepjenn is offline
    Registered User
    Join Date
    07-20-2012
    Location
    Ottawa Canada
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Trying to use the COUNTIFS formula, but might need SUMPRODUCT instead?

    Hi Haseeb,

    This still doesnt work for me, so I dont know what Im doing wrong...I cant upload the file, so I have included a picture. The result I should be getting is 5.




    forms.jpg
    Reply With QuoteRegister To Reply
  8. 02-21-2013, 11:49 AM #8
    jeepjenn
    jeepjenn is offline
    Registered User
    Join Date
    07-20-2012
    Location
    Ottawa Canada
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Trying to use the COUNTIFS formula, but might need SUMPRODUCT instead?

    Sorry, I uploaded the wrong pic...column D and E are switched in this pic...it should be D=template required by and E=proposed date sent to client
    Reply With QuoteRegister To Reply
  9. 02-21-2013, 06:22 PM #9
    Haseeb Avarakkan
    Haseeb Avarakkan is offline
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Trying to use the COUNTIFS formula, but might need SUMPRODUCT instead?

    Could you please post the formula you tried?
    Reply With QuoteRegister To Reply
  10. 02-22-2013, 12:04 PM #10
    jeepjenn
    jeepjenn is offline
    Registered User
    Join Date
    07-20-2012
    Location
    Ottawa Canada
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Trying to use the COUNTIFS formula, but might need SUMPRODUCT instead?

    In the attached photo, you can see the formula, the data and the result as well as what the expected result should be.

    Jennifer


    form3.jpg
    Reply With QuoteRegister To Reply
  11. 02-23-2013, 07:01 PM #11
    Haseeb Avarakkan
    Haseeb Avarakkan is offline
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Trying to use the COUNTIFS formula, but might need SUMPRODUCT instead?

    Hello Jennifer,

    Could you please re-check your conditions posted on first post?
    If column E, G, H, I, J and K are all filled (with dates) and column L is empty (no date)
    Currently column J is empty, so always results will be zero. I am not sure how you have got the answer 7. Am I miss something?
    Reply With QuoteRegister To Reply
  12. 02-25-2013, 11:55 AM #12
    jeepjenn
    jeepjenn is offline
    Registered User
    Join Date
    07-20-2012
    Location
    Ottawa Canada
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Trying to use the COUNTIFS formula, but might need SUMPRODUCT instead?

    YOU are right my friend! Sorry for doubting you...guess its because I'm stressed!

    Thank YOu for your help!!
    Reply With QuoteRegister To Reply
  13. 02-25-2013, 12:02 PM #13
    jeepjenn
    jeepjenn is offline
    Registered User
    Join Date
    07-20-2012
    Location
    Ottawa Canada
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Trying to use the COUNTIFS formula, but might need SUMPRODUCT instead?

    Soooo...what part would I change if I wanted to use the exact same formula, but adjust it to the following:

    If column E, G, H, I and J are filled and Columns J and K are blank?
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1