sum values of cells in google sheets if the result of their sum with adjacent cell meets a condition

Question

Suppose I have the following data in a spreadsheet, where the first row contains the column headings and the first column contains the row index (for reference):

  ||A  |  B  |  C  |
==||================
0 ||2  |  3  |  Y  |
--||----------------
1 ||2  |  4  |  Y  |
--||----------------
2 ||3  |  5  |  N  |
--||----------------
3 ||8  |  3  |  Y  |
--||----------------

How can I get the sum of all the values in column B for which b - a >= 1 && c == "Y", in Google Sheets and Excel?

So essentially, the sum should factor in only rows 0 and 1 in which case the result should be 7.

I know this sounds like a very specific question but I did not know how else to describe it other than by example. The answer should be applicable in other similar scenarios.

Thanks for the help.

[Edit] In response to people voting down due to lack of research, well, I've tried to use the sumif() function but I got stuck immediately on the condition part as I am not sure how to compare the current item in the aggregation with another cell. I also tried to use the sumifs() function which allows for multiple criteria, but also to no avail. As for my research, I searched on Google but could not find anything, possibly due to my inability to express the requirement in a manner suitable for a google query. Therefore, I have presented the above as a way of explaining my requirement by example.

I hope this helps.

I appreciate that this may not be possible to do with the simple built in formulas. If this is the case, please mention it as that would also be useful to know.

Thank you.

Answer 1

Excel: =SUMPRODUCT(((B:B-A:A)>=1)*(C:C="Y")*(B:B))

Untested, but let me know if it works. Next time, remember to add an example of code/formulas that you have already tried and what error you ran into.

---

Edit:

Tested it, here is a screenshot of it working with your example data (disregard the fact that my Excel is in spanish)

This works by intersecting both logical tests (that is, performing a logical AND): (B-A)>=1 AND C="Y". Here you can see the result of each logical test and then, finally, where it evaluates to TRUE it returns the value in column B; where it's FALSE, it returns 0. Finally, it sums the values in the result array.