2 queries - using sumproduct for entire columns / avoiding manual date calculations

morning all

Thanks to the guys that helped me yesterday with a couple of queries. I have a couple more on the same s/sheet that I am working through today.

I have a spreadsheet of running and cycle data (see attached).

1. Whenever I wish to add a new entry I insert a line at the top of the existing entries, i.e. a new Row 5, and then I manually copy the formulas from the old K5:KAA to the new line which is now K5:KAA. However I find that the SUMPRODUCT formulas I am using to calculate the columns N 'Total Weeks Run Miles' and R 'Total Cycle Miles' do not fully take account of the new line. I don't want to manually have to alter the formulas each time. I had considered whether the SUMPRODUCT formulas could refer to whole columns, so instead of using the following for example in N5:

=SUMPRODUCT(($B5:$B$246="Run")*($G5:$G$246=G5)*($H5:$H$246=H5)*($I5:$I$246))

I could use something more like this:

=SUMPRODUCT((B:B="Run")*(G:G=G5)*(H:H=H5)*(I:I)

However it didn't like that. Is there a way I can solve this?

Query 2.
I have to use a variety of columns to calculate the dates I use. Some of these seem to have been necessary, for example columns D-G, with the dates broken up per column for day of the week, date, month and year. This in itself isn't a problem, but I have to use two other columns, which I keep separate from the main data, these are in columns Z and AA. AA is used to convert the month in text, to a number. Z is used to convert the day, month and year (via CONCATENATE) to a date format that excel can then use to calculate the number of days between runs/cycles etc.

Is there a much more simple way to embed these formulas within others in the s/sheet, and therefore remove the need to have these two separate columns. I did consider having just one date entry column, say column D, which would contain day, month, year - but discarded this idea as I like to be able to autofilter by month, year etc.

Any suggestions? Sorry this is rather a long message!

Dean

Hi, for your query 2, you can enter the month as 09/01/2012 - and set the format as mmmm. It will display only september. And in your combined date formula, use date(G5, Month(F5), E5)

For query 1, instead of B5:B246, have some buffer rows for new additions - like B5:B1000. So that you need not modify the formula everytime a new row is added.

cheers sindhus - i shall give them a try!

Actually the solution to query 1 does not work. The new row 5 will have the correct formulas, however the rows immediately below do not reference to the new row 5 to calculate the total weekly miles, they only calculate from row 6, or row 7, respectively dependent on their relative position. The new N5 shows this formula:

=SUMPRODUCT(($B$5:$B$1000="Run")*($G$5:$G$1000=$G$5)*($H$5:$H$1000=$H$5)*($I$5:$I$1000))

Whilst the row 6 (the old row 5) shows this which does not now include the new row 5:

=SUMPRODUCT(($B$6:$B$1001="Run")*($G$6:$G$1001=$G$6)*($H$6:$H$1001=$H$6)*($I$6:$I$1001))

Any suggestions?

Dean

My understanding of your requirement is, say you have two data in B2, B3. And say, C3 = B3-B2. When you enter a row between B2 and B3, the formula will change to B4-B2. But you want to retain it as B3-B2.

To do that way, you can try formula using indirect C3:= indirect("B" & Row()) - indrect("B" & Row()-1)

Hi Sindhus, I think the issue here is that my data is in rows 5-247, and I insert a new line above row 5, not say, between rows 5 and 6. this seems to be causing the issue.

So i'm guessing that the formula you provided isn't relevant now (I wasn't really clear on how it would be used anyway). Could you assist?

Ok then just enter your address within indirect. Indirect("$B$5:$B$247"). Since the address is entered as a text, it wont change when you are inserting new row.