# Easier way to write this formula?

#### FinancialAnalystKid

##### Well-known Member
=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.

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
What's wrong with the formula you tried?

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

Results in a #VALUE! error

My usual culprit with a #value error in sumproduct is that the named ranges have different dimensions (i.e. one goes from A1:A17 and the other goes from B1:B16). Would that fix the issue?

edit. oops. Double posted the same thing. See above.

They are all based on the same dynamic named range. I checked to see if it is covering the range completely for each column and they are.

Is that last formula correct? Am I missing something?

FinancialAnalystKid said:
They are all based on the same dynamic named range. I checked to see if it is covering the range completely for each column and they are.

Is that last formula correct? Am I missing something?

#VALUE error would result from the presence uf text values like formula blanks in the ranges to sum.

I use the first formula the forumla works without the #VALUE! error. If there was some text formating within that range it would give me an error too? But when I consolodate the formula using:
=SUMPRODUCT(--(C_MONTH=\$A6),--(C_PLAN=\$H\$3),(C_VC_T+C_ANC_T+C_ROOM_T+C_SUPPORT_T))
I get an error.

FinancialAnalystKid said:
I use the first formula the forumla works without the #VALUE! error. If there was some text formating within that range it would give me an error too? But when I consolodate the formula using:
=SUMPRODUCT(--(C_MONTH=\$A6),--(C_PLAN=\$H\$3),(C_VC_T+C_ANC_T+C_ROOM_T+C_SUPPORT_T))
I get an error.

Any of the named ranges include column headers?

Here's what my Range refers to:

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

All ranges refer to respective columns.

Replies
0
Views
168
Replies
2
Views
191
Replies
4
Views
309
Replies
13
Views
382
Replies
3
Views
144

1,221,418
Messages
6,159,791
Members
451,589
Latest member
Harold14

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back