Formula Question...

  • Thread starter Thread starter sarge355
  • Start date Start date
S

sarge355

I have a general ledger spreadsheet with a annual and monthly tab followed by
the individual account tabs. On the monthly tab I have the following formula:

=SUMIF('9000-Muck Site'!$J:$J,"="&(TEXT(I$5,"mmm-yy")),'9000-Muck Site'!$D:$D)

My problem is the range is not picking up all of the rows. Based on the
formula above it stopped at D96. How do I extend (or show) other rows past
D96?

Thanks!
 
You probably misunderstood. Wigi meant that although they might look like
numbers they might be text. Find one that is not picked up and use

=ISTEXT(D97)


copy down, if any of those formulas return TRUE then there are text
values. However it might be more likely that it is the first test that fails


TEXT(I$5,"mmm-yy") returns a text value like Jul-08, maybe some of the
values in
column J have leading or trailing spaces?

=SUMIF('9000-Muck Site'!$J:$J,"="&(TEXT(I$5,"mmm-yy")),'9000-Muck
Site'!$D:$D)

There are some obsolete characters, in your case you can use

=SUMIF('9000-Muck Site'!$J:$J,TEXT(I$5,"mmm-yy"),'9000-Muck Site'!$D:$D)

no need for the equal sign

Try this

=SUMPRODUCT(--('9000-Muck Site'!$J1:$J1000=TEXT(I$5,"mmm-yy")),'9000-Muck
Site'!$D1:$D1000)




--


Regards,


Peo Sjoblom
 
Wigi,

The tab on my spreadsheet is listing individual sales based on the sales
date. That, in turn, ties back to the monthly tab to give me a breakdown of
sales by month. It stopped calculating at cell D96. Can you provide me an
example of how the formula should read? Thanks!!!
 
Back
Top