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

  • Thread starter Thread starter Steven
  • Start date Start date
S

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
 
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.
 
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)
 
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
 
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
 
Back
Top