Easier way to write this formula?

[FinancialAnalystKid]

=SUMPRODUCT(--(C_MONTH=$A6),--(C_PLAN=$H$3),C_VC_T)+SUMPRODUCT(--(C_MONTH=$A6),--(C_PLAN=$H$3),C_ANC_T)+SUMPRODUCT(--(C_MONTH=$A6),--(C_PLAN=$H$3),C_ROOM_T)+SUMPRODUCT(--(C_MONTH=$A6),--(C_PLAN=$H$3),C_SUPPORT_T)

I tried:
=SUMPRODUCT(--(C_MONTH=$A6),--(C_PLAN=$H$3),(C_VC_T+C_ANC_T+C_ROOM_T+C_SUPPORT_T))

The first two parts are the same as you see.
SUMPRODUCT(--(C_MONTH=$A6),--(C_PLAN=$H$3),

But I have to sum four other columns.

 

[Zack Barresse]

Can you change your range to ...

=CUMULATIVE!$O$2:INDEX(CUMULATIVE!$O:$O,MATCH(REPT("z",255),CUMULATIVE!$O:$O))

.. as COUNTA will skip blanks. This is where you may be seeing an error.

 

[Aladin Akyurek]

They all have headers...

Here's what my Range refers to:

=OFFSET(CUMULATIVE!$O$1,0,0,COUNTA(CUMULATIVE!$O:$O),1)

All columns have the same formula.

You need to exclude the row with headers/labels from the definitions.

 

[Brian from Maui]

They all have headers...

Here's what my Range refers to:

=OFFSET(CUMULATIVE!$O$1,0,0,COUNTA(CUMULATIVE!$O:$O),1)

All columns have the same formula.

In the range to sum, if the named range includes a header, that maybe the culprit of the #VALUE error.

As an aside, here's a dynamic named range using Index/Match instead of Offset and Counta

=$B$2:INDEX($B:$B,MATCH(BigStr,$B:$B))

Where BigStr is defined as,

=REPT("z",255)

 

[FinancialAnalystKid]

Can you change your range to ...

=CUMULATIVE!$O$2:INDEX(CUMULATIVE!$O:$O,MATCH(REPT("z",255),CUMULATIVE!$O:$O))

.. as COUNTA will skip blanks. This is where you may be seeing an error.

I tried this with all my dynamic ranges and all my formulas turned results to #VALUE!

When i check to see what range it covers it only selects the first two rows of that column.

Do I use REPT("z",255) on all of them? What does this do?

 

[Zack Barresse]

It is the largest textual value excel will look for in doing this type of search in a column. I'd suggest you use one number for the row. So I'd define one name as the largest row you will have, then use that name in your other defined names. You are most likely getting that error because of mis-aligned ranges. They must be of equal shape/size to work in such a fashion.

The part to replace in such a formula (as above) would be ...

MATCH(REPT("z",255),CUMULATIVE!$O:$O)

.. for the largest known row. Or define the name MaxRow which Refers To the above formula. Then in each of your defined names, use it like this ...

=CUMULATIVE!$O$2:INDEX(CUMULATIVE!$O:$O,MaxRow)


HTH