how could i replace this formula?? (tried using #names)

[Steven]

this formula works :

=SUMIF(M!$C$13:$C$228,$D8,M!$O$13:$O$228)

i tried replacing with :

=SUMIF(m_cause_cells,$D8,m_total_time)

m_cause_cells =M!$C$13:$C$51,M!$C$72:$C$110,M!$C$131:$C$169,M!$C$190:$C$228
m_total_time =M!$O$13:$O$51,M!$O$72:$O$110,M!$O$131:$O$169,M!$O$190:$O$228

should i change the names to something like :

m_cause_cells =M!$C$13:$C$51+M!$C$72:$C$110+M!$C$131:$C$169+M!$C$190:$C$228
m_total_time =M!$O$13:$O$51+M!$O$72:$O$110+M!$O$131:$O$169+M!$O$190:$O$228

eek its 4am! thanks for you help,

Steve

 

[Earl Kiosterud]

Steven,

I looked up the SUMIF function to see if there are restrictions on using
named ranges with multiple areas. Found nothing. Looked in "Excel 2002
Bible" too. Nothing I could find. But such a range name simply doesn't
seem to work. It yields #VALUE.

I suspect you'll have to use separate SUMIFs for each area in m_cause_cells,
using a separate name (or cell reference) for each area. Same with
m_total_time.

 

[Frank Kabel]

Hi Steven
try the following formula
=SUMPRODUCT(--(M!$C$13:$C$228=$D8),--(MOD(ROW(M!$C$13:$C$228)-13,59)<=3
8),M!$O$13:$O$228)

 

[Steven]

Could you explain the benefits of using your formula over this one
=SUMIF(M!$C$13:$C$228,$D8,M!$O$13:$O$228) as im just learning please?
and maybe send you my files so you can see better what im trying to do?

Thanks,

Steve

 

[Frank Kabel]

Hi
the benefi of my formula is that it would recognize your
splitted range as indicated in your previous post. If you
have a contingeneous range just use your SUMIF formula. No
need for SUMPRODUCT.
My formula would do your your example ranges of three
interrupted ranges