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

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

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.

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.

OK - losing my hair now!

I created a new spreadsheet and as you say it all works fine on random data, just not with mine! Below is my data from the "Daily Individual Sales" worksheet which is now titled Sheet 1. I hoped that you could cut and paste this date into excel but dont think it will work??
The columns are L and M, the "TOTAL DAYS" title starts at row 4. Where there is no data, its either because no days were sold or the date has not yet occured. Each row represents a different person. There are 6 people. I have tried to show this on the first set of data below:

TOTAL DAYS TRANS
0 0
0 0
0 0
43 12
56 14
10 2

TOTAL DAYS TRANS
0


40 11
80 17
64 15

TOTAL DAYS TRANS
50 8
85 8
27 4

TOTAL DAYS TRANS
25 7
0
0 7

TOTAL DAYS TRANS
0


46 12

76 16

TOTAL DAYS TRANS
0


42 11
8 4
47 12

TOTAL DAYS TRANS
61 13
40 9
22 6


TOTAL DAYS TRANS
31 13
61 15
16 4

TOTAL DAYS TRANS
69 18
75 14
44 11


TOTAL DAYS TRANS
0

28 7
34 8
53 9

TOTAL DAYS TRANS
0


58 15
66 15
2 1

TOTAL DAYS TRANS
51 12
29 7
0 0

TOTAL DAYS TRANS
57 17
31 8
20 8

TOTAL DAYS TRANS
0

28 11
62 13
63 18

TOTAL DAYS TRANS
0


63 20

88 19

TOTAL DAYS TRANS
0


80 24

88 21

TOTAL DAYS TRANS
41 8
87 22
10 3

TOTAL DAYS TRANS
56 9
55 13
34 9

TOTAL DAYS TRANS
0


57 11
58 17
27 8

TOTAL DAYS TRANS
0


24 8
41 12
19 5

TOTAL DAYS TRANS
69 12
55 11
2 1

TOTAL DAYS TRANS
52 10
30 6
9 2

TOTAL DAYS TRANS
0

TOTAL DAYS TRANS
0

TOTAL DAYS TRANS
0

TOTAL DAYS TRANS
0

TOTAL DAYS TRANS

TOTAL DAYS TRANS

TOTAL DAYS TRANS

TOTAL DAYS TRANS

TOTAL DAYS TRANS

Can you see anything weird expalining why the formula:

=SUMPRODUCT((MOD(ROW(Sheet1!$L$5:$L$245),8)=5)*(Sheet1!$L5:$L245))

would not work? The cell i am putting the formula in is Sheet 2, cell A1.

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.

Rob, a few things:

1. In your sheet, I suggest turning on automatic calculation (Tools->Options->Calculation->Automatic). That threw me off for a minute when any change I made to the formula wouldn't work.

2. On the 'Daily Individual Sales' and 'Monthly Breakdown' sheets, anywhere you have used =SUM(X/Y), where X and Y are cell references (A1, M18, etc..), you don't need the SUM() wrapper. Simply =X/Y will work.

***BUT, if you want to hide all of the #DIV/0! errors (due to numbers being divided by 0 or blank cells) then use =IF(ISERROR(X/Y,"",X/Y)).

3. Most importantly, I figured out why the SUMPRODUCT formula wasn't working in your sheet: you have text in that column which was generating a #VALUE! error. We can work around this, though, by using the double unary form of SUMPRODUCT instead, like so:

Please Login or Register  to view this content.

I also changed the "=5" part to "=MOD(ROW(L5),8)" so that if you drag the formula down or up, it will update the row reference and determine the remainder for comparison. Essentially, "MOD(ROW(L5),8" equals 5, "MOD(ROW(L8),8" equals 0, "MOD(ROW(L10),8" equals 2, etc.

I will finish updating the formulas and hiding errors for you and get the sheet back to you by tomorrow morning.

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!

Originally Posted by Rob_UK

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!

Hi,

to help yourself and Paul with the formula, in Agent1 C6, something like

=INDIRECT("'Daily Individual Sales'!K"&(ROW()-5)*8-3)

F6

=INDIRECT("'Daily Individual Sales'!G"&((ROW()-5)*8-3))

and in I6

=IF(ISERROR(SUM(INDIRECT("'Daily Individual Sales'!C"&((ROW()-5)*8-3)&":E"&((ROW()-5)*8-3)))/INDIRECT("'Daily Individual Sales'!L"&((ROW()-5)*8-3))),0%,SUM(INDIRECT("'Daily Individual Sales'!C"&((ROW()-5)*8-3)&":E"&((ROW()-5)*8-3)))/INDIRECT("'Daily Individual Sales'!L"&((ROW()-5)*8-3)))

or as

Please Login or Register  to view this content.

to obviate the web spaces,

will formula fill down the columns, but I'll pass on the bar thanks.

hth
---
added, the -3 adjuster could be contained in a cell, say A1, being -3, -2, -1, 0, +1, +2 for the 6 sheets,
then "&((ROW()-5)*8-3) would become "&((ROW()-5)*8+$A$1) the formula could then be used in all 6 sheets, however, this might be considered 'overkill' unless the number of agents is set to increase, and a simple modification to each of the 3 formulae be made for each sheet.
---

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.