1

So I'm trying to use sumifs with indirect and address to sum all of the previous rows in a column, however, I receive a #value error when I attempt. I was wondering if someone could help point out the error in my formula or process? Here is the formula I'm using:

=SUMIFS(INDIRECT(ADDRESS(2,[Value])&":"&ADDRESS(ROW()-1,[Value])),[Category],"s",[Type],2)

enter image description here

If you look at the dataset it also seems like the sumif_test is malfunctioning and summing the whole column instead of the specified range.

Thank you all for your time!

Improve this question
5
  • Does this work? =SUMIFS(Table1[[#Headers],[value]]:[@value], Table1[[#Headers],[category]]:[@category], "s", Table1[[#Headers],[type]]:[@type], 2)
    – user385793
    Dec 11, 2019 at 14:02
  • This is working on the sample data but when I try to apply it to the actual dataset I get an error that the syntax of the name isn't correct and it highlights [@value]; I can fix it by changing all of the [@]s to [@[]]s (eg; [@[Value]] but then it gives a value of 0 no matter what.
    – TotalRapture
    Dec 11, 2019 at 15:41
  • Ahhh looks like sumifs can't use OR() arguments, only AND(). My formula had an OR() in the sumifs so I wrapped the sumifs in a SUM() and changed the removed the OR(), wrapping the arguments in {}. Eg; Sumifs(OR(a,s)) became Sum(Sumifs({a,s}))
    – TotalRapture
    Dec 11, 2019 at 16:56
  • Thanks for the help! I'm sorry, I'm new to the site and not sure how to designate your answers as helpful or give credit.
    – TotalRapture
    Dec 11, 2019 at 20:17
  • Now that we've got the problems worked out, I've created an answer below which can be accepted as the answer.
    – user385793
    Dec 11, 2019 at 23:45

2 Answers 2

Reset to default
0

For an OR condition, wrap the SUMIFS in a SUM.

=SUM(SUMIFS(Table1[[#Headers],[value]]:[@value], Table1[[#Headers],[category]]:[@category], {"a", "s"}, Table1[[#Headers],[type]]:[@type], 2))
Improve this answer
0

You can try using the below, I use this to SUM running totals (or all previous ROWS), not sure if it will transfer to a SUMIFS.

I have assumed Value is in Column A and starts Row 1, adjust the formula if this is not the case.

=SUMIFS($A$2:A2,[Category],"s",[Type],2)

OR

=SUMIFS($A$2:A2,$B$2:B2,"s",$C$2:C2,2)

This allows the SUM range to always start at row 2, and only extend to your current ROW.

Let me know if this doesn't work and I will remove it as an answer.

Improve this answer
2
  • The first will not work as the ranges will not be the same size. The second should work.
    – Scott Craner
    Dec 11, 2019 at 14:45
  • The second one is working but I need to use the table headers as references.
    – TotalRapture
    Dec 11, 2019 at 15:38

You must log in to answer this question.

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