Easier way to write this formula?

FinancialAnalystKid

Well-known Member
Joined
Oct 14, 2004
Messages
779
=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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365
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))
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365
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?
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365

ADVERTISEMENT

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

FinancialAnalystKid

Well-known Member
Joined
Oct 14, 2004
Messages
779
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?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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.
 

FinancialAnalystKid

Well-known Member
Joined
Oct 14, 2004
Messages
779
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.
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
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?
 

FinancialAnalystKid

Well-known Member
Joined
Oct 14, 2004
Messages
779
They all have 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.
 

Forum statistics

Threads
1,148,528
Messages
5,747,221
Members
424,069
Latest member
kamkwok1hh

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top