CODEWITHSUNDEEP
google-sheetsarray-formulas
Sean
Sean

Reputation: 73

Sumif with arrayformula and horizontal and vertical ranges

I'm trying to essentially do a "sumif" with criteria that are in both horizontal and vertical ranges in google sheets. I found a reference that says excel can do this with arrayformulas here.

I think I've copied the syntax according to how arrayformulas work in gsheets, but I can't get it to work. I made a quick sample sheet here, where the "sum" column should match date in column B with Row 3 and "yes" for column L to give a result of "2" for every date, but for some reason I'm getting an error.

What's even stranger is that a perfect match of this formula on my actual dataset returns a numerical date each time... I'm sure this can be done with more intelligent index matching (which I would be fine with!) but haven't been able to get that to work.

Thanks in advance!

Upvotes: 1

Views: 3029

Answers (1)

Jacob Edmond
Jacob Edmond

Reputation: 808

You last range should exclude row 3:

=arrayformula(sum(if($G$3:$K$3=B4,if($L$4:$L$6="Yes",$G$4:$K$6))))

Upvotes: 1

Related Questions