0

enter image description here

I want to use the function SUMIFS to sum the Price value of only those rows in which the value in column B is not present in the range of "Things" in column F. Here, the answer should be 80 (Apple + Guava).

I can't use the SUMIF function as the actual sheet has multiple criteria.

Improve this question

1 Answer 1

Reset to default
2

You can try the formula below. It substract the things from the total of all items.

=SUM(price,-(SUMIFS(price,list,things)))

where:

  • price: range from C4:C8
  • list: range from B4:B8
  • things: range from F4:F5

Use the Name Manager to modify the ranges based on your real values.

enter image description here

Improve this answer
5
  • Thank you for your answer. In the actual case, the list of "Things" in column F is not just 2 entries but a list of 100+ entries which will keep on changing. So this won't work in that case.
    – Antriksh Johri
    Apr 4, 2021 at 8:52
  • @AntrikshJohri See edit.
    – Reddy Lutonadio
    Apr 4, 2021 at 9:53
  • hey, i know this may not be the place, but i cant find anything anywhere else. im trying to sum the values in cells in odd rows and sum the values in cells in even rows. ive tried many diff formulas, but none seem to work. here is an example of what i expected to work in summing values in odd rows: =SUMIF(G$2:G$999999, ISODD(ROW()),G$2:G$999999) but this returns 0 for some reason? any idea why this might be?
    – oldboy
    Sep 3, 2022 at 3:30
  • @oldboy You must create a question related to your issue (I will gladly help). Answering here as it is not related to this question goes against the rules.
    – Reddy Lutonadio
    Sep 3, 2022 at 7:30
  • fair enough. i posted a Q. your help would be greatly appreciated.
    – oldboy
    Sep 4, 2022 at 0:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .