Countifs across columns and dependient on criteria

1. Countifs across columns and dependient on criteria

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

2. 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. 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. 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)

5. 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. Re: Countifs across columns and dependient on criteria

thank you this worked!

7. Re: Countifs across columns and dependient on criteria

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

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