+ Reply to Thread
Results 1 to 12 of 12

SUMPRODUCT Formula, using OR Logic

  1. #1
    Registered User
    Join Date
    01-03-2018
    Location
    Ohio, USA
    MS-Off Ver
    2016
    Posts
    11

    SUMPRODUCT Formula, using OR Logic

    Good afternoon,

    I'm in a bit of a predicament. I only have a basic understanding of the SUMPRODUCT formula to begin with and have been using it within this training tracker for a couple months. It's just come to my attention that on the "Attendance Reports" tab, the "Attended" will only count cells that have a number >0. I would like to figure out how to have it populate based upon the cell containing either 1) a percentage or 2) the letter "A". How would I format that into the formula? Thanks!


    Here is the formula, workbook in question is attached with sensitive data redacted.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: SUMPRODUCT Formula, using OR Logic

    See if this helps you out. http://www.utteraccess.com/wiki/Array_Formulas

    It's an article on array formulas but it uses SUMPRODUCT as an example of the logic.

    When using * instead of -- in SUMPRODUCT, you can think of * as AND and + as OR.

    SUMPRODUCT((Range A=B)*((Range C=1) + (Range C="A")) * (Range D))

    This gets the sum of D where Range A = B AND (Range C is either 1 OR "A")
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: SUMPRODUCT Formula, using OR Logic

    Hi, welcome to the forum

    My very first comment to you would be to try and avoid using merged cells like you have there. They cause all sorts of problems with formulas, and it is often possible to achieve the same result just by adjusting the row height and column width

    2nd, you could probably shorten that formula to this...
    =SUMPRODUCT( IF($F$28="All",1,('Training Data'!B$5:B$1497=F$28))*('Training Data'!C$5:C$1497>=F$30)*('Training Data'!C$5:C$1497<=F$32)*('Training Data'!$D$4:$CF$4=$F$34)*('Training Data'!D$3:CF$3=F$36)*(ISTEXT('Training Data'!$D$5:$CF$1497))*('Training Data'!$D$5:$CF$1497="N"))

    Im not sure which selection to use to test for what you want, but have you tried to just add another condition like you did with "N"?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    01-03-2018
    Location
    Ohio, USA
    MS-Off Ver
    2016
    Posts
    11

    Re: SUMPRODUCT Formula, using OR Logic

    Quote Originally Posted by FDibbins View Post
    Hi, welcome to the forum

    My very first comment to you would be to try and avoid using merged cells like you have there. They cause all sorts of problems with formulas, and it is often possible to achieve the same result just by adjusting the row height and column width

    2nd, you could probably shorten that formula to this...
    =SUMPRODUCT( IF($F$28="All",1,('Training Data'!B$5:B$1497=F$28))*('Training Data'!C$5:C$1497>=F$30)*('Training Data'!C$5:C$1497<=F$32)*('Training Data'!$D$4:$CF$4=$F$34)*('Training Data'!D$3:CF$3=F$36)*(ISTEXT('Training Data'!$D$5:$CF$1497))*('Training Data'!$D$5:$CF$1497="N"))

    Im not sure which selection to use to test for what you want, but have you tried to just add another condition like you did with "N"?
    The "N" on the Attendance Reports is meant to show a No show. The only data that will trigger a count for the No Show is an "N", unlike the Attended which may or may not include a test score. Sometimes we simply need to track attendance without an assessment. Therefore, I'd require the formula to count both a number or the letter "A".

    Capture.PNG

    The merged cells is something I do actively try to avoid, I'm surprised I didn't actually do as you suggested in the first place. However, I will definitely be fixing that because, as you stated, they are needlessly difficult to work with at times.

  5. #5
    Registered User
    Join Date
    01-03-2018
    Location
    Ohio, USA
    MS-Off Ver
    2016
    Posts
    11

    Re: SUMPRODUCT Formula, using OR Logic

    Quote Originally Posted by dflak View Post
    See if this helps you out.

    It's an article on array formulas but it uses SUMPRODUCT as an example of the logic.

    When using * instead of -- in SUMPRODUCT, you can think of * as AND and + as OR.

    SUMPRODUCT((Range A=B)*((Range C=1) + (Range C="A")) * (Range D))

    This gets the sum of D where Range A = B AND (Range C is either 1 OR "A")
    That's is what I have gathered from my research, the bit about * being AND and + being OR... Unfortunately, it doesn't seem to want to work the way I think it should when I do it... I attempted to make it work originally using this:

    Please Login or Register  to view this content.

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: SUMPRODUCT Formula, using OR Logic

    One more suggestion. Since you are using Excel 2016, you have tables. If you convert the data range to an Excel tables, you get two benefits:
    1. if you add more data at the end of the range, you do not have to adjust the formula - Tables "know" how big they are and formulas automatically adjust to reference exactly the right amount of rows.
    2. You can use column header names in the formulas. Table1[DOH] easier to remember than what $C$5:$C$1497 means.

    Here is more information on Excel Tables: http://www.utteraccess.com/wiki/Tables_in_Excel.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: SUMPRODUCT Formula, using OR Logic

    Quote Originally Posted by YUSATrain View Post
    ....Therefore, I'd require the formula to count both a number or the letter "A"....
    Again, not which parts of your data to test this against, but Im thinking that if 1 of your arguments used COUNTA(), that may help?

    If you could indicate a (small) section of data that I could use, as well as what you expect, I could do some testing

  8. #8
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: SUMPRODUCT Formula, using OR Logic

    Please Login or Register  to view this content.
    I'm used to seeing SUMPRODUCT applied to an nx1 array (a single column or row). I've not seen it applied to multiple rows.

  9. #9
    Registered User
    Join Date
    01-03-2018
    Location
    Ohio, USA
    MS-Off Ver
    2016
    Posts
    11

    Re: SUMPRODUCT Formula, using OR Logic

    Quote Originally Posted by FDibbins View Post
    Again, not which parts of your data to test this against, but Im thinking that if 1 of your arguments used COUNTA(), that may help?

    If you could indicate a (small) section of data that I could use, as well as what you expect, I could do some testing
    Apologies, attached is a version of the original form except everything is hidden that isn't directly relevant to my goal. The first tab displays a series of data, some "N", "A" and percentages. I am trying to find a way to have both "A" and the percentages count in the "Attended" section of the second tab.

    Thank you for your help! I apologize for being difficult.
    Attached Files Attached Files
    Last edited by YUSATrain; 05-21-2018 at 03:34 PM. Reason: Sheet did not attach the first time...

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: SUMPRODUCT Formula, using OR Logic

    This appears to give the correct result


    =IF($F$28="All",SUMPRODUCT(('Training Data'!$C$5:$C$568>=F$30)*('Training Data'!$C$5:$C$568<=F$32)*('Training Data'!$D$4:$CF$4=$F$34)*('Training Data'!$D$3:$CF$3=F$36)*('Training Data'!$D$5:$CF$568>0)*('Training Data'!$D$5:$CF$568<>"N")),SUMPRODUCT(('Training Data'!$B$5:$B$568=F$28)*('Training Data'!$C$5:$C$568>=F$30)*('Training Data'!$C$5:$C$568<=F$32)*('Training Data'!$D$4:$CF$4=$F$34)*('Training Data'!$D$3:$CF$3=F$36)*('Training Data'!$D$5:$CF$568>0)*('Training Data'!$D$5:$CF$568<>"N")))

  11. #11
    Registered User
    Join Date
    01-03-2018
    Location
    Ohio, USA
    MS-Off Ver
    2016
    Posts
    11

    Re: SUMPRODUCT Formula, using OR Logic

    Quote Originally Posted by JohnTopley View Post
    This appears to give the correct result


    =IF($F$28="All",SUMPRODUCT(('Training Data'!$C$5:$C$568>=F$30)*('Training Data'!$C$5:$C$568<=F$32)*('Training Data'!$D$4:$CF$4=$F$34)*('Training Data'!$D$3:$CF$3=F$36)*('Training Data'!$D$5:$CF$568>0)*('Training Data'!$D$5:$CF$568<>"N")),SUMPRODUCT(('Training Data'!$B$5:$B$568=F$28)*('Training Data'!$C$5:$C$568>=F$30)*('Training Data'!$C$5:$C$568<=F$32)*('Training Data'!$D$4:$CF$4=$F$34)*('Training Data'!$D$3:$CF$3=F$36)*('Training Data'!$D$5:$CF$568>0)*('Training Data'!$D$5:$CF$568<>"N")))
    Thank you for your reply, Mr. Topley. That formula does in fact work for the given set of data, except it also counts the "P" (Planned) as attended. However, I've gotten around that problem by creating a helper cell and subtracting the "Planned" from the "Attended".

    Thank you everyone for your replies! It's very much appreciated. I will go ahead and mark this thread as solved at this point. Even the suggestions given that weren't directly related to my issue here will definitely come in handy as I get more familiar with the advanced formulas in excel.

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: SUMPRODUCT Formula, using OR Logic

    I am happy you got where you wanted to be, and thanks for the feedback

+ 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. Sum data ignoring text using the sumproduct with logic
    By Larry.LeBlanc@O in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-26-2014, 10:45 AM
  2. logic behind sumproduct
    By ammartino44 in forum Excel General
    Replies: 4
    Last Post: 09-17-2014, 11:48 AM
  3. [SOLVED] Sumproduct with OR Logic
    By daffodil11 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-17-2014, 09:27 PM
  4. [SOLVED] SUMPRODUCT. using AND-OR logic with text
    By FAL22 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-26-2013, 04:34 PM
  5. SUMPRODUCT with multiple logic and without duplicates
    By cberas in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-25-2013, 03:53 PM
  6. formula logic (sumproduct,countif)
    By TROBFP in forum Excel General
    Replies: 1
    Last Post: 04-22-2007, 08:48 PM
  7. Replies: 3
    Last Post: 03-28-2006, 03:56 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