+ Reply to Thread
Results 1 to 8 of 8

How can I add alternate cells in a row if the adjacent column meets a certain criteria.

  1. #1
    Registered User
    Join Date
    12-10-2020
    Location
    India
    MS-Off Ver
    2015
    Posts
    3

    Post How can I add alternate cells in a row if the adjacent column meets a certain criteria.

    Let me explain this with an example - If column A, C, E, G are for denoting expenses [just numbers] and column B, D, F, H denote different methods used in expenses like cash, card, paytm etc selected via dropdown data validation; then I want to add the cells A, C, E and G and get a total cash expenditure of the day in column I. Can anybody help me solve this query? Thanks in advance for your time and effort.

    I have edited this post and uploaded an excel file. In the attached file how can I calculate the total cash expenditure on 01/12/2020. As you can see that some of the payments on that day have been made through card the others through cash. So i want that In K3 I can get a total of all the cells which are cash payments. Then I will replicate this formula for the rest of the cells in that column by dragging the formula. Sorry for not being able to explain myself in the first time.
    Attached Files Attached Files
    Last edited by mastexcelforum; 12-12-2020 at 03:36 AM.

  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,757

    Re: How can I add alternate cells in a row if the adjacent column meets a certain criteria

    =SUMPRODUCT(--($A$3:$G$10="Cash"),$B$3:$H$10)

    see file. Next time,please remember that a picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

    So, read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.
    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
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    43,973

    Re: How can I add alternate cells in a row if the adjacent column meets a certain criteria

    @Glenn: if I’ve read the question right, the numbers and descriptions are the other way round.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    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,757

    Re: How can I add alternate cells in a row if the adjacent column meets a certain criteria

    Aha! So far no coffee this morning. Only tea and Corn Flakes.

    If that's the case:
    =SUMPRODUCT(--($B$3:$H$10="Cash"),$A$3:$G$10)
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 12-10-2020 at 04:24 AM.

  5. #5
    Registered User
    Join Date
    12-10-2020
    Location
    India
    MS-Off Ver
    2015
    Posts
    3

    Re: How can I add alternate cells in a row if the adjacent column meets a certain criteria

    Thank you for replying. But I guess I wasn't able to explain my query well. Although your answer seems to be a solution of a more complex query that I may need in future and will save it.

    Sorry to bother you again, but I have edited my original post and uploaded a small portion of the excel sheet to explain my query. Can you please find a file named Textbook attached to the original query and look into it when you have time to spare. Thanks a lot!
    Last edited by mastexcelforum; 12-12-2020 at 03:41 AM.

  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,757

    Re: How can I add alternate cells in a row if the adjacent column meets a certain criteria

    Is this what you want??


    =SUMPRODUCT(--(D3:J3="Cash"),C3:I3)

    copied down. If not, add manually calculated results to your file.
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,496

    Re: How can I add alternate cells in a row if the adjacent column meets a certain criteria

    Quote Originally Posted by mastexcelforum View Post
    Let me explain this with an example - If column A, C, E, G are for denoting expenses [just numbers] and column B, D, F, H denote different methods used in expenses like cash, card, paytm etc
    Column A is date.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  8. #8
    Registered User
    Join Date
    12-10-2020
    Location
    India
    MS-Off Ver
    2015
    Posts
    3

    Re: How can I add alternate cells in a row if the adjacent column meets a certain criteria

    Bang On! I have spent not less than 10 hours to figure this out in vain. And I am sure you must have done this in not more than 10 secs. I was able to apply it to the complete column by dragging the formula. Thank you so much Glenn, you really saved my day!!

+ 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. Replies: 4
    Last Post: 11-04-2020, 12:47 PM
  2. Sum Up Cells Until Adjacent Cell Meets Criteria
    By cocostar88 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-27-2013, 02:41 PM
  3. [SOLVED] If value meets criteria, highlight adjacent cells. repeat across sheets
    By matrix_machine in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-04-2012, 07:07 PM
  4. [SOLVED] Trying to count cells greater than # in one column if another column meets criteria
    By lorne17 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-14-2012, 07:01 PM
  5. [SOLVED] Create a formula to add cells in one column if it meets the criteria for another column
    By Tamarissa in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-23-2012, 11:27 AM
  6. Replies: 0
    Last Post: 07-22-2012, 01:05 PM
  7. Add numbers if adjacent column meets criteria
    By billybob013 in forum Excel General
    Replies: 1
    Last Post: 08-08-2008, 11:37 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