+ Reply to Thread
Results 1 to 15 of 15

Ignore blank cells, calculate operations based on multi-column criteria with duplicates

  1. #1
    Registered User
    Join Date
    07-08-2022
    Location
    Los Angeles, CA
    MS-Off Ver
    2016
    Posts
    12

    Ignore blank cells, calculate operations based on multi-column criteria with duplicates

    Hello,

    I am trying to find several metrics on my spreadsheet (attached) but the duplicate rows in column A and multiple criteria needed are giving me problems.

    1. For average time difference between column C and column B, I have 2 formulas (L2 and L3) but they don't allow for blank cells. If there are, it results in #DIV/0!. I would like this formula to work with blank cells as well, by taking average difference between each column, and ignoring blank cells because I plan to add more entries as time goes on.

    2. Is there a formula that calculates the total of each number in column E based on its respective item in column A, not counting duplicates in column A. Again I'll need this formula to ignore blank cells for future entries. For this example, I'll need the formula to calculate the total amount of Long trades only. The answer is in M6.

    3. Is there a formula for finding the average of column G per each column A group. For example, if column A has duplicates, I need the average of the respective rows in column G, then to add that average to the next group of averages and so on. The average is based on 8 trades, not 15. Answer in M11

    4. Is there also a formula to calculate the average of of column G based on column A group and column H. So for each duplicate or group in column A, it calculates the average in column G if its respective row in column H says "Win", then add averages together for a total average. Answer in M12.

    5. Lastly, is there a formula to find the largest value in column D based on column A, but if there are duplicates, the respective rows in column D must be added together, then the formula find which value in that column is the largest. Answer in M14

    I've tried different variations of SUMPRODUCT and COUNTIF but keep getting errors, #DIV/0! or 0. If some of the formulas are the same or similar when providing an answer, you can let me know and I'll figure it out. Any help is much appreciated!
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Ignore blank cells, calculate operations based on multi-column criteria with duplicate

    Here's a few of them. I'm away for a while, but will try to look back later. variants of:

    =SUM(INDEX(($E$2:$E$20="Long")*($A$2:$A$20<>"")/COUNTIFS($E$2:$E$20,$E$2:$E$20&"",$A$2:$A$20,$A$2:$A$20&""),0))

    see file.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    07-08-2022
    Location
    Los Angeles, CA
    MS-Off Ver
    2016
    Posts
    12

    Re: Ignore blank cells, calculate operations based on multi-column criteria with duplicate

    Glenn, thank you so much! These worked perfectly and you even completed more than I asked, very much appreciated. I was able to figure out the largest value for $ amount and % amount with: =MAX(SUMIF(A2:A20,A2:A20,D2,D20). However I am still struggling to figure out the average time to ignore the blank cells from 17-20. I believe it has something to do with <>"" but my excel knowledge is limited when it comes to this, SUMPRODUCT, INDEX, COUNTIF.

    I'll write back if I figure it out in the meantime, otherwise I'll await your response. Safe travels!

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Ignore blank cells, calculate operations based on multi-column criteria with duplicate

    You might try converting the range A1:H16 into an Excel table (Ctrl + t) so that you don't have blanks.
    When you add a new record in row 17 the formula in cell L3 will update.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    07-08-2022
    Location
    Los Angeles, CA
    MS-Off Ver
    2016
    Posts
    12

    Re: Ignore blank cells, calculate operations based on multi-column criteria with duplicate

    Hi JeteMc, I just tried that but when I add a new row to the table, the formula results in#DIV/0!

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Ignore blank cells, calculate operations based on multi-column criteria with duplicate

    Hi. I forgot about this.

    Are there ever blank rows in the MIDDLE of your data (as opposed to at the END of your data)?

    If teh answer is NO, we can make this a LOT simpler.

  7. #7
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: Ignore blank cells, calculate operations based on multi-column criteria with duplicate

    1.
    =SUM(IF(C2:C20<>"",C2:C20,0))/SUM(IF(C2:C20<>"",1,0))-SUM(IF(B2:B20<>"",B2:B20,0))/SUM(IF(B2:B20<>"",1,0))

    2.
    =SUM(IF(FREQUENCY(IF(E2:E20="Long",A2:A20,""),A2:A20),1,0))
    =SUM(IF(FREQUENCY(IF((E2:E20="Long")*(H2:H20="Win"),A2:A20,""),A2:A20),1,0))
    (replace "Long" with "Short" to change)

    3.
    =SUM(G2:G20)/SUM(IF(FREQUENCY(A2:A20,A2:A20),1,0))

    4.
    =SUM(IF(H2:H20="Win",G2:G20,0))/SUM(IF(FREQUENCY(IF(H2:H20="Win",A2:A20,""),A2:A20),1,0))

    5.
    I'm a bit stumped in Excel 2016 to do it in a single cell. I'll have a think. In 365 it would be:
    =MAX(BYCOL(IF(A2:A20=TRANSPOSE(UNIQUE(FILTER(A2:A20,A2:A20<>""))),D2:D20,0),LAMBDA(col,SUM(col))))

    Note 3 and 4 match your example but wasn't quite what I thought you meant from reading your description.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Ignore blank cells, calculate operations based on multi-column criteria with duplicate

    I can somewhat replicate the issue, as in when a new row is first inserted in the middle or added to the end of the table cell L3 displays #DIV/0!.
    However, whether inserting or adding, as soon as a number is placed in the corresponding cell in column A cell L3 displays a number.
    Just to be sure that we are on the same page, the array entered formula that I see in cell L3 of the file attached to post #1 is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    If you get the #DIV/0! even when data is placed in the inserted/added row then please upload a sample so that we can attempt to troubleshoot.
    Let us know if you have any questions.

  9. #9
    Registered User
    Join Date
    07-08-2022
    Location
    Los Angeles, CA
    MS-Off Ver
    2016
    Posts
    12

    Re: Ignore blank cells, calculate operations based on multi-column criteria with duplicate

    Hi Glenn, no there wouldn't be any blank rows in the middle of the data. Just a continuous table, row by row, with data in the preceding rows. Curious to know your solution as well!
    Last edited by davidcato; 07-14-2022 at 11:29 PM.

  10. #10
    Registered User
    Join Date
    07-08-2022
    Location
    Los Angeles, CA
    MS-Off Ver
    2016
    Posts
    12

    Re: Ignore blank cells, calculate operations based on multi-column criteria with duplicate

    Thank you Nick, those formulas worked as well! I figured out #5 luckily so I'm good for now.

  11. #11
    Registered User
    Join Date
    07-08-2022
    Location
    Los Angeles, CA
    MS-Off Ver
    2016
    Posts
    12

    Re: Ignore blank cells, calculate operations based on multi-column criteria with duplicate

    JeteMc, I stand corrected. You are right. When you enter in the time, it updates the formula and I get the result not the #DIV/0!. Thank you for pointing this out to me

  12. #12
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: Ignore blank cells, calculate operations based on multi-column criteria with duplicate

    Quote Originally Posted by davidcato View Post
    Thank you Nick, those formulas worked as well! I figured out #5 luckily so I'm good for now.
    You're welcome. Please could you share your solution for #5? I'm curious, and it might help others who have the same issue.

  13. #13
    Registered User
    Join Date
    07-08-2022
    Location
    Los Angeles, CA
    MS-Off Ver
    2016
    Posts
    12

    Re: Ignore blank cells, calculate operations based on multi-column criteria with duplicate

    Quote Originally Posted by nick.williams View Post
    You're welcome. Please could you share your solution for #5? I'm curious, and it might help others who have the same issue.
    This is what I used and it's working so far:
    =MAX(SUMIF(A2:A20,A2:A20,D2,D20).

  14. #14
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: Ignore blank cells, calculate operations based on multi-column criteria with duplicate

    Thanks, nice solution. Had me confused - I think you did a typo and the last comma should be a colon.

  15. #15
    Registered User
    Join Date
    07-08-2022
    Location
    Los Angeles, CA
    MS-Off Ver
    2016
    Posts
    12

    Re: Ignore blank cells, calculate operations based on multi-column criteria with duplicate

    Yes you're right, should be a colon! Thank you

+ 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. [SOLVED] COUNTING Text Cells with Duplicates Based on Multi-base criteria
    By TMains in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-14-2022, 09:58 PM
  2. Replies: 11
    Last Post: 06-06-2021, 01:42 AM
  3. Formula to ignore blank cells and calculate cells having numbers only
    By bodmas in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-18-2020, 08:40 PM
  4. Count text formula based on multi-criteria excluding duplicates
    By eyeope in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-07-2019, 12:26 PM
  5. [SOLVED] Ignore Blank Cells for Criteria based Combobox list
    By ShakJames in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-22-2016, 09:51 AM
  6. Replies: 3
    Last Post: 05-23-2013, 07:50 PM
  7. how to ignore blank in multi-column combobox
    By dlh in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-22-2010, 08:49 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1