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.23

296.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.

Reply ↓  Report •

✔ Best Answer
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))

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code



#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


Reply ↓  Report •

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

Reply ↓  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.

Reply ↓  Report •

Related Solutions

#4
August 2, 2018 at 14:15:33
✔ Best Answer
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))

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  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.

Reply ↓  Report •

#6
Reply ↓  Report •

Ask Question