adding non-adjacent cells depending on another cell

[clayton]

I am adding totals for multiple products for 3 shifts.
In cells CY7, DA7, DC7, DE7, DG7, DI7, DK7, DM7, DO7, AND DQ7 are the shift indicators which will either have 1, 2, 3 or "" in them.

If there is say a 1 in cell CY7 then CY8 will be added to first shifts total for that product. If its a 2 then it will be added to the 2nd shifts total.

I've tried several methods but because the cells are not adacent I can't get anything to work. Any Ideas?

[Bryan Hessey]

I am adding totals for multiple products for 3 shifts.
In cells CY7, DA7, DC7, DE7, DG7, DI7, DK7, DM7, DO7, AND DQ7 are the shift indicators which will either have 1, 2, 3 or "" in them.

If there is say a 1 in cell CY7 then CY8 will be added to first shifts total for that product. If its a 2 then it will be added to the 2nd shifts total.

I've tried several methods but because the cells are not adacent I can't get anything to work. Any Ideas?

Hi,

what range of cells? - just CY7(CY8) to DQ7(DR7)?

---

[Carim]

Hi,

Most probably the sumproduct() formula can solve your problem ...
Do you mind uploading a zipped copy of your worksheet for a precise answer ...

HTH
Carim

[Bryan Hessey]

I am adding totals for multiple products for 3 shifts.
In cells CY7, DA7, DC7, DE7, DG7, DI7, DK7, DM7, DO7, AND DQ7 are the shift indicators which will either have 1, 2, 3 or "" in them.

If there is say a 1 in cell CY7 then CY8 will be added to first shifts total for that product. If its a 2 then it will be added to the 2nd shifts total.

I've tried several methods but because the cells are not adacent I can't get anything to work. Any Ideas?

Hi,

For the range CY to CR - one possibility is a UD Function,

ie, in the cell requiring the totals, put

=CountCY(1)
=CountCY(2)
=CountCY(3)
etc

then copy the code to a Module
(rightmouse the worksheet tab, select 'View Code'
Insert, Module,
Paste this code there

Please Login or Register  to view this content.

and let me know how you go

note, the entry must be on the same row as the figures.
---

[Bryan Hessey]

Hi,

Most probably the sumproduct() formula can solve your problem ...
Do you mind uploading a zipped copy of your worksheet for a precise answer ...

HTH
Carim

Hi Carim,

the problem I thought with SumProduct is that 2 (or more) consecutive cells having the same value would produce an incorrect result, ie, a Value of 2 would add the following cell 'type' to the 2 total, and 6 consecucutive '2's would be alarming.

Cheers.
---

[clayton]

Hi,

what range of cells? - just CY7(CY8) to DQ7(DR7)?

---

Yes, this would be for one products total for that shift. I would need to repeat the process for 2nd shift in another cell and again for 3rd shift. And then I have to duplicate all the above for each product.
But yes, those are the ranges.

[clayton]

Hi,

For the range CY to CR - one possibility is a UD Function,

ie, in the cell requiring the totals, put

=CountCY(1)
=CountCY(2)
=CountCY(3)
etc

then copy the code to a Module
(rightmouse the worksheet tab, select 'View Code'
Insert, Module,
Paste this code there

Please Login or Register  to view this content.

and let me know how you go

note, the entry must be on the same row as the figures.
---

The result of the above only gives me "0" no matter what I put for the shifts and amounts.?

EDIT: In playing around with entering numbers and such it seems to only update "sometimes". Like if code is executed then it works but if no script runs then it wont run itself. Something like that lol

One more thing. If I add a column to the left of this set of cell then it will break this script correct? If so then is there any way I can use named cells? I already have the cells named so it would be a matter of if this script can do it or not.
I just don't want to break it in a year and not know how I broke it.

[daddylonglegs]

You can try this formula for shift 3

=SUMPRODUCT(--(CY7:DQ7=3),1-MOD(COLUMN(CY7:DQ7)-COLUMN(CY7),2),CY8:DQ8)

change the 3 to a 2 etc. for other shifts

There might be an easier way, what's in the intermediate cells, CZ7, CB7 etc?
Do you have headers, e.g. in CY1:DQ1, if so what's in those?

[clayton]

You can try this formula for shift 3

=SUMPRODUCT(--(CY7:DQ7=3),1-MOD(COLUMN(CY7:DQ7)-COLUMN(CY7),2),CY8:DQ8)

change the 3 to a 2 etc. for other shifts

There might be an easier way, what's in the intermediate cells, CZ7, CB7 etc?
Do you have headers, e.g. in CY1:DQ1, if so what's in those?

The above code would not work for me.
Here is a small example of what I am working with:

Please Login or Register  to view this content.

[daddylonglegs]

Why didn't that work, what result did you get, zero, error, something else?

If your headers, "shift" total etc. are in row 6 try this formula for shift 3

=SUMPRODUCT(--(CY6:DQ6="shift"),--(CY7:DQ7=3),CY8:DQ8)

[daddylonglegs]

If there is say a 1 in cell CY7 then CY8 will be added to first shifts total for that product. If its a 2 then it will be added to the 2nd shifts total.

Do you mean CY8 here or CZ7?

If it's the latter

=SUMPRODUCT(--(CY6:DQ6="shift"),--(CY7:DQ7=3),CZ7:DR7)

[Carim]

Clayton,

As I suggested earlier a sample worksheet with a few data in a zipped format would make it much easier to give you a precise answer ...

HTH
Carim

[clayton]

Do you mean CY8 here or CZ7?

If it's the latter

=SUMPRODUCT(--(CY6:DQ6="shift"),--(CY7:DQ7=3),CZ7:DR7)

This appears to be working although I altered it a bit to fit the actual cells shift and totals are in.

=SUMPRODUCT(--(CY5:DQ5="shift"),--(CY6:DQ6=1),CZ6:DR6)

I have to leave for work now but I will be working with this more a little later on.


@Carim
This is a very simple workbook and the example I showed is about all there is to it (multiplied by 3 shifts and 11 products)
If for some reason this doesn't work out correctly for all my scenarios then I will post the workbook.

Thank you for your responses