Solved SumIfs, Offset, and CountA to sum the last entries.

August 1, 2018 at 19:37:39
Specs: Windows 10
 I am using SUMIFS to calculate two amounts dynamically using Offset and CountA. I need to find the bottom entry in each row and sum. When evaluating the formula, it finds both the correct amounts, and then gives me a #value error. Where did I go wrong?Formula:=SUMIFS(Count_Table[[CashRoth]:[CashIRA]],Count_Table[CashRoth],OFFSET('RAW DATA'!U7,COUNTA(Count_Table[CashRoth])-1,0),Count_Table[CashIRA],OFFSET('RAW DATA'!V7,COUNTA(Count_Table[CashIRA])-1,0))Formula after using Evaluate Formula:SUMIFS(\$U\$7:\$V\$101,\$U\$7:\$U\$101,296.09,\$V\$7:\$V\$101,901.23296.09 and 901.23 are the two values I want to sum, but the next step returns #value.Where is my error?

See More: SumIfs, Offset, and CountA to sum the last entries.

August 2, 2018 at 14:15:33
 Without setting up tables to test your formula against, the first thing I noticed was that your sum_range was 2 columns wide.I'm pretty sure that the sum_range in SUMIFS can only be one column wide.You might be able to eliminate the helper cells with something like this:=SUM(OFFSET('Raw Data'!U7,COUNTA(Count_Table[CashRoth])-1,0), OFFSET('Raw Data'!V7,COUNTA(Count_Table[CashIRA])-1,0))

#1
August 2, 2018 at 12:55:38
 Bottom entry in each row?I'm having a little trouble visualizing that.message edited by DerbyDad03

Report •

#2
August 2, 2018 at 13:29:42
 My bad, last entry in each column.

Report •

#3
August 2, 2018 at 13:51:25
 Since the two OFFSET functions worked properly, I have used two helper cells for a workaround. Problem solved.

Report •

Related Solutions

#4
August 2, 2018 at 14:15:33
 Without setting up tables to test your formula against, the first thing I noticed was that your sum_range was 2 columns wide.I'm pretty sure that the sum_range in SUMIFS can only be one column wide.You might be able to eliminate the helper cells with something like this:=SUM(OFFSET('Raw Data'!U7,COUNTA(Count_Table[CashRoth])-1,0), OFFSET('Raw Data'!V7,COUNTA(Count_Table[CashIRA])-1,0))

Report •

#5
August 3, 2018 at 12:54:22
 Thank you very much DerbyDad! For some reason, I like to try and make things complicated. Changing to a simple SUM worked perfectly.

Report •

#6
August 3, 2018 at 15:28:04