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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,105
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,105
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,105
Office Version
  1. 365
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,210
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,181,786
Messages
5,932,044
Members
436,816
Latest member
Composh

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