+ Reply to Thread
Results 1 to 7 of 7

Countifs across columns and dependient on criteria

  1. #1
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    669

    Countifs across columns and dependient on criteria

    file uploaded now, sorry it's not uploaded before

    Please refer to cell GK29 which I want to have a formula of sum if

    I am trying to calculate everything in columns GK as well as column GR when the cells in column GO have a yes. I am expecting 692.71 as the value but i am not getting this... any help appreciated.

    thank you
    Attached Files Attached Files
    Last edited by rayted; 05-04-2021 at 08:51 AM.
    Thanks,

    R.



  2. #2
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,734

    Re: Countifs across columns and dependient on criteria

    =SUMIFS(GK3:GK21,GK3:GK21, "<>#N/A",$GO$3:$GO$21,"yes")+SUMIFS(GR3:GR21,GR3:GR21,"Yes")


    the bit in red can not be true it can not be a yes and a value the criteria range should be another column?

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,892

    Re: Countifs across columns and dependient on criteria

    Formula is incorrect

    =SUMIFS(GK3:GK21,GK3:GK21, "<>#N/A",$GO$3:$GO$21,"yes")+SUMIFS(GR3:GR21,GO3:GO21,"Yes")

    Yo have #REF! error in GR13:

    with this formula you have value 9197

    in Gk29

    784.69

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,021

    Re: Countifs across columns and dependient on criteria

    Maybe try

    =SUMIFS(GK3:GK21,GK3:GK21,">0",$GO$3:$GO$21,"yes")+AGGREGATE(9,6,GR3:GR21)
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    669

    Re: Countifs across columns and dependient on criteria

    sorry davsth my reply didn't send/save but it's fixed now. thanks for looking

  6. #6
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    669

    Re: Countifs across columns and dependient on criteria

    thank you this worked!

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,892

    Re: Countifs across columns and dependient on criteria

    =sumproduct(((gk3:gk20)+(gr3:gr20))*(go3:go20="yes"))

+ 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] Countifs if criteria is based on columns and rows (by date)
    By kingripper in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-25-2021, 03:28 AM
  2. [SOLVED] Countifs - 2 different columns, same criteria
    By rayted in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-24-2019, 05:01 AM
  3. [SOLVED] countifs with criteria at same columns
    By shj92922 in forum Excel General
    Replies: 5
    Last Post: 08-26-2018, 10:34 AM
  4. Countifs - multiple columns with the same criteria
    By danici in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-17-2017, 07:07 AM
  5. [SOLVED] Countifs on multiple columns & criteria
    By tabkaz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-13-2016, 02:59 AM
  6. Countifs Help with multiple columns in one criteria
    By itobon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-30-2015, 02:03 PM
  7. Replies: 4
    Last Post: 07-20-2012, 07:51 AM

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