How to take data from multiple cells that are not together?

Hiya,

Prob confused everyone with that title!

I am trying to ask excel to look at the information that appears in certain cells,add it together and then divide it by another cells data. The problem is the info to add up is not all together so i cant say, for example, =SUM(A1:A5). The cells I need info from are, however, in a pattern of every 8 rows. Example:

C5,C13,C21,C29. Rather than have to type every cell into the formula I am sure you clever people have an easier solution!!

Thanks again

Rob

This might work for you:

=SUMPRODUCT((MOD(ROW($C$1:$C$500),8)=5)*($C$1:$C$500))

Change your range as needed, C1:C500 is an example. This will sum every cell in the column where the row() value divided by 8 leaves a remainder of 5. (C5, C13, C21, etc.)

Thanks for suggestion. I have put in what I think is right but does not appear to be working!

The actual data I need is is a worksheet called Daily Individual Sales, in cells L5, L13, L21 and then in intervals of 8 up to L245. Therefore in the cell I wanted the total of the cells to appear I typed:

=SUMPRODUCT((MOD(ROW('Daily Individual Sales'!$L$5:'Daily Individual Sales'!$L$245),8)=5)*('Daily Individual Sales'!$L$5:'Daily Individual Sales'!$L$245))

But all I get is the #VALUE! ( and yes there is data in cells L5, L13 etc!)

Am I missing something obvious?!?!

Rob, I setup a spreadsheet based on your specs. I have two sheets, "Sheet1" and "Daily Individual Sales".

In column L of Daily Individual Sales, I filled in the number 1 from cells L1 to L250.

In cell B18 of Sheet1 (random cell I chose), I put the formula you provided and it returned the proper sum (31, since there are 31 cells between 5 and 245 counting by 8, inclusive of 5 and 245).

If you can't figure out why you're getting the value error, try zipping and uploading the sheet and we can take a look.

OK had to take a break from this pesky spreadhsheet but back now with the bullet between my teeth!!

Are you saying that thie formula will tell me how many "cells" there are between L5 and L245 ?? IF so, I will file that away for future use. What I need is the sum of the values in every 8th cell between L5 and L245. So if my cells were as follows:
L5 10
L13 5
L21 8
L29 3

then the magic formula i put in at cell x1 would give me the answer 26 as this is L5 + L13 + L21 + L29.

I am not able to upload from this work pc ( dam national companies! ) but if it makes things easier I can email it to my home pc and then upload it.

Thanks for your patience


Rob

Hi Rob,

I just re-created the environment from our original discussion, and when I changed the values in L1:L250 from 1's to 2's, the formula returned double the amount. (31 changed to 62).

I also changed the values in L5, L13, L21, etc.. and the formula returned the correct SUM each time.

That would mean it is actually summing every 8th row in your range, not just counting every 8th row.

Rob, can you zip up and post a copy of your spreadsheet? It would be much easier to test on your file than for me to continue testing on ones I make myself that don't seem to have issues with the formula.

Thanks.

Yes, will do tonight from home. Sorry to mess you around!


Rob

OK! I am back home and have de-personalised the spreadsheet and removed any unneccesary sheets. If you take a look at the first sheet this is what we track each agents "sales" on. The next spreadsheet then totals up the figures from the 1st spreadsheet and the final chart spreadsheets give an "easy to view layout and chart for printing at the end of each month. There are assorted issues which I have made notes of in red highlight on each of the 3 sheet types!!! If anyone is really bored and wants to try fix them for me that would be great! Paul, if you can get the sumproduct to work then you are a genius!

Thanks guys!

Rob

Here's the workbook back, with a few other notes...

1. On your agent sheets, in the FPO% and Insurance % columns, the "TOTAL" row cells (C38 and I38) have nice long formulas to average the percentages not equal to zero, as shown below:

=SUM(C6:C36)/SUMPRODUCT(--(LARGE((C6:C36),ROW(INDIRECT("1:"&COUNT(C6:C36))))<>0))
=SUM(I6:I36)/SUMPRODUCT(--(LARGE((I6:I36),ROW(INDIRECT("1:"&COUNT(I6:I36))))<>0))

But, this can be much simpler, like so:

=SUM(C6:C36)/COUNTIF(C6:C36,">0")
=SUM(I6:I36)/COUNTIF(I6:I36,">0")

Or even slightly shorter, using an array formula (you have to press CTRL+SHIFT+ENTER after entering the formula, not just ENTER):

=AVERAGE(IF(C6:C36>0,C6:C36))
=AVERAGE(IF(I6:I36>0,I6:I36))

I updated all of the Agent sheets cells C38 and I38 with the SUM/COUNTIF type formula above so you don't have to remember to use CTRL+SHIFT+ENTER if you ever go into those cells.

================================================

2. On your Agent 1 sheet, you had a formula in column I:
=SUM('Daily Individual Sales'!C5:E5)/'Daily Individual Sales'!L5

I added error correction to show 0% when a #DIV/0! error occurs, like so:
=IF(ISERROR(SUM('Daily Individual Sales'!C5:E5)/'Daily Individual Sales'!L5),0%,SUM('Daily Individual Sales'!C5:E5)/'Daily Individual Sales'!L5)

I did not update column I in sheets Agent 2 through Agent 6, though, since it had a different formula in column I on those sheets, which evaluated to a #REF! error. You were dividing by a "hidden" column of numbers (gray font/gray background) in col G? I'll let you decide what should be in those cells since I don't know an insurance % formula from a hole in the wall, to be honest.

If you use the formula I added on Agent 1, you will have to create/adjust every formula from I6:I36 on each sheet (Agent 2 - Agent 6). This is 155 formula edits, but you'll get into a rhythm after a few.

Simplest way would be to copy I6:I36 from Agent 1 and paste it into Agent 2 - 6 sheets. Then adjust the starting cell for each sheet. For example, on Agent 1 cell I6, the cell references in the formula are C5, E5 and L5. In I7, they're C13, E13 and L13. Now on Agent 2 cell I6, that same formula should reference C6, E6 and L6; followed by C14, E14 and L14 in I7. On Agent 3 cell I6, the formula should reference C7, E7 and L7 followed by C15, E15 and L15 in I7. I think you get the picture, and I couldn't come up with an easy way to automatically allow you to drag the formula down and have each of those increment by 8 (boy would that have made it easy!).

================================================

3. I updated cell A1 on all of the sheets to reference 'Daily Individual Sales'!A1. Some of your #REF! errors are probably due to the fact that a sheet or cells were removed.

================================================

4. On the 'Daily Individual Sales' sheet I added error correction on columns H and K to hide #DIV/0! errors.

================================================

Well, I hope that solves most of the issues, even though there's a bit more work for you to enter in those Agent Insurance% formulas.. if you need further assistance be sure to let us know!

Paul,

Thank you so much for all the effort you have put in . I am about to go over your thorough notes but wanted to say thanks up front!!

The grey blanked out cells you mentioned I cant even remeber why I did them. Will look at that as well!

Ill post back after ive had a proper look but you are the top man!!

Rob

Yes, its offical. Paul is a STAR!

Your changes work great and I managed to type in my 155 formulas ok. ( CHOCOLATE BAR TO WHOEVER COMES UP WITH A WAY TO AUTOFILL CORRECTLY IN THAT SITUATION)

I got rid of the grey boxes. They were nothing to do with the insurance calculations, just a running total of the COE for use in the chart. I have now moved these figs down to bottom of sheet alongside the other chart info.

The sheets look great now. I just have one more favour! My final questions for you is............. " On the Daliy Individual Sales sheet where no FPO sales are made, there is now a fancy blank which looks great. However this is creating a blank on the Agent sheet in the FPO column. That means that my source data for the chart does not convert it into NA# so my graph jumps to 0 and back which I was trying to avoid. If you look at the sheet and FPO graph it will prob make more sense. Is there a way of fixing this fix?!?!? That will be the last question I promise......well for tonight anyway!

Thanks again Paul and anyone else that has helped me since I set out on this journey!

Bryan, thanks for the assistance with those formulas. Would have taken me a bit to come up with that last one.

Rob, as for your 0's and NA()'s.. you should be able to change the formula in column K (FPO %) on the Daily Individual Sales sheet to:

=IF(ISERROR(J5/M5),0%,J5/M5)

It's currently set to =IF(ISERROR(J5/M5),"",J5/M5, which is making it blank.

If you want to hide the 0.00%'s in just that column, you can create a Conditional Format on that column's cells so that 'Cell Value Is' -> 'Equal to' -> 0, and then set the font color to match the background color. When your formula evaluates to 0, you won't see it!

If you want to hide all 0 values in your spreadsheets, use the Tools -> Options -> View -> Window Options section -> un-check Zero Values.

Great stuff Bryan and Paul!

Thanks again for your expert advice! Its amazing how powerful and complex Excel is until you really get your teeth into it. Without guys like you all us "basic users" would be in the kaka indefinently!!!!

Cheers guys.