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?

✔ 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

Bottom entry in each row?I'm having a little trouble visualizing that.

message edited by DerbyDad03

Since the two OFFSET functions worked properly, I have used two helper cells for a workaround. Problem solved.

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

Thank you very much DerbyDad! For some reason, I like to try and make things complicated. Changing to a simple SUM worked perfectly.

Ask Your Question

Weekly Poll