Sumproduct of every Nth column changing every 9 columns

Hi everyone,

I had the same problem and your formula helped me a lot!
thank you very much!

I have another problem for which I can't find the solution.
I need to indicate in my formula that I want the calculation to be done for all the third, sixth and ninth column from a column that regularly changes letter.

I have three colors, green, pink and blue that repeat.

1,2,3 / 3 green
4, 5,6 / 6 rose
7, 8, 9 / 9 bleu
If I tell it to take into account the Nth column from AD8 (the 1/3 green) it does it but my calculation is no longer good when I get to AM8 (1/10) .
It seems to me that I can correct this with an INDEX and MATCH function but I can't find how.

Does anyone have an idea how I can do it?

Here is my formula:

SUMPRODUCT(--(MOD(COLUMN($AD8:LM8)-COLUMN($AD8)+1;9)=0);--($AD8:LM8>0);-(ISFORMULA($AD8:LM8));$AD8:LM8)

Thanks a lot !

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

done !

thanks for your help

OK, I have been playing around with this for a while and I have created an example file. That's the godd news. The bad news is that I don't know how to apply this in your file.

In the hope that it will mean more to you than it does to me, I'm sharing the file.

I also note that one of your SUMPRODUCT conditions has a single minus rather than the more usual double negative. That will reverse the value of the result.

I think this: -(ISFORMULA($AD8:LM8)); should be this: --(ISFORMULA($AD8:LM8));

I need to use the formulas in the cells: F8, H8 and J8 for the negative values and G8, I8, K8 for the positive values.

You'll find attached and image where is clearer what I'm trying to do I think.

Either I say to excel to do the sumproduct starting from the AD8 column and it'll be every 3rd, 6th and 9th column or every third column if we consider every color separately. Do you see what I mean ?


BTW the double -- helped me to resolve another problem for the positive and the negative values. I didn't know it should be -- than -

I'm tying to understand your file but not sure I do it to succeed.

Is this the formula I need to use :

SUM(IF( (COLUMN($AD$1:$AZ$1)>=COLUMN($AD$1) ) * ((MOD(COLUMN($AD$1:$AZ$1);9))=0); $AD1:$AZ1;""))

I put another capture of the final I result I'm looking to have
hope is clearer

I'm going to need to leave this until tomorrow.

Could you just confirm the version of Excel you are using.

Some expected answers in the sheet would be a help!!


Is this what you want?

=SUMPRODUCT(--(1+MOD((COLUMN(P8:KR8)-COLUMN(P8)),9)=1)*N(+P8:KR8))

in F8

=SUMPRODUCT(($P$4:$KY$4=$F$6)*($P8:$KY8<0),($P8:$KY8))

Who are you talking to?

My message was for you Glenn Kennedy, I didn't have the JohnTopley's message yet at the same moment.

this formula works perfectly as I wanted

=SUMPRODUCT(--(1+MOD((COLUMN(P8:KR8)-COLUMN(P8)),9)=1)*N(+P8:KR8))

I just need to consider either the - neither the + values at the same time in both columns.

For example if I have

-5 , -5 , -5 and further 20 in the column (-) it will appears -15 and next to it in the (+) 20 at the same time. That's why I have two different columns for both values

Back soon.

Try:

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Or, a bit neater

=SUMPRODUCT(($P$4:$KZ$4=F$6)*($R8:$LB8<>"")*(N(+$R8:$LB8))*(N(+$R8:$LB8)<0))
=SUMPRODUCT(($P$4:$KZ$4=F$6)*($R8:$LB8<>"")*(N(+$R8:$LB8))*(N(+$R8:$LB8)>0))
=SUMPRODUCT(($P$4:$KZ$4=H$6)*($R8:$LB8<>"")*(N(+$R8:$LB8))*(N(+$R8:$LB8)<0))
=SUMPRODUCT(($P$4:$KZ$4=H$6)*($R8:$LB8<>"")*(N(+$R8:$LB8))*(N(+$R8:$LB8)>0))
=SUMPRODUCT(($P$4:$KZ$4=J$6)*($R8:$LB8<>"")*(N(+$R8:$LB8))*(N(+$R8:$LB8)<0))
=SUMPRODUCT(($P$4:$KZ$4=J$6)*($R8:$LB8<>"")*(N(+$R8:$LB8))*(N(+$R8:$LB8)>0))

Originally Posted by TMS

Or, a bit neater

=SUMPRODUCT(($P$4:$KZ$4=F$6)*($R8:$LB8<>"")*(N(+$R8:$LB8))*(N(+$R8:$LB8)<0))
=SUMPRODUCT(($P$4:$KZ$4=F$6)*($R8:$LB8<>"")*(N(+$R8:$LB8))*(N(+$R8:$LB8)>0))
=SUMPRODUCT(($P$4:$KZ$4=H$6)*($R8:$LB8<>"")*(N(+$R8:$LB8))*(N(+$R8:$LB8)<0))
=SUMPRODUCT(($P$4:$KZ$4=H$6)*($R8:$LB8<>"")*(N(+$R8:$LB8))*(N(+$R8:$LB8)>0))
=SUMPRODUCT(($P$4:$KZ$4=J$6)*($R8:$LB8<>"")*(N(+$R8:$LB8))*(N(+$R8:$LB8)<0))
=SUMPRODUCT(($P$4:$KZ$4=J$6)*($R8:$LB8<>"")*(N(+$R8:$LB8))*(N(+$R8:$LB8)>0))

It works but is there any possibility to have + and - values at the same time on the same line in both columns + and - ?

With your formula if I have -20 and 5 it doesn't appear -20 in (-) and in 5 (+) but -15

you see what I mean ?

Please see the attached excel example file

Thank you in any case !

Please post a file with data / formulae not pictures [and did you try post #9 ?]

Sry about that

I add an excel file with an example inside

Try

in F8

=SUMPRODUCT(($P$4:$KX$4=$F$6)*($R8:$KZ8<0),($R8:$KZ8))

and

G8

=SUMPRODUCT(($P$4:$KX$4=$F$6)*($R8:$KZ8>0),($R8:$KZ8))

@JT: Looking good Not sure why, I kept looking at and wanting to "over-engineer" it!

Originally Posted by JohnTopley

Try

in F8

=SUMPRODUCT(($P$4:$KX$4=$F$6)*($R8:$KZ8<0),($R8:$KZ8))

and

G8

=SUMPRODUCT(($P$4:$KX$4=$F$6)*($R8:$KZ8>0),($R8:$KZ8))

It works !

as effective as it is short
It's impressive

thank you very much
tell me one of your favorite perfumes and I'll send you a sample :D

I've just tried it in my original excel file cause I didn't put here the original and I have #N/A , I'll try to understand why, is exactly the same file

I didn't reply, because I didn't understand what you wanted. Now I do, I think. see file.

It works !

great

You all guys tell me please one of your favourite perfumes :d

the stars are coming too of course!

BTW the values to calculate will be in R8 not in P8... that was not clear to me!!

Try it now.

no, it's OK
I didn't precise it, it's normal.

I'm trying to understand the formula as well, not just waiting for you to do it and put it in my original file :d

It >>should<< be OK in the file I posted at #25

@Glenn: I don't think it was clear to any of us. There are formulae in all those cells but none of them produce values which can be tested and accumulated.

Sry about that
You all did it even if it wasn't very clear.

Thank you !

You're welcome.



If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Originally Posted by Glenn Kennedy

BTW the values to calculate will be in R8 not in P8... that was not clear to me!!

Try it now.

Hi Glenn,

the formula works perfectly but I realised that when I have negatif and positive values at the same time it doesn't consider the positif and the result is based only on the negatif one.

So I need to make my formula to do the substraction of positive valu (if there is one) from the final result based on the negative one.

Hope is clear. I gave an example in the file.

Let me know if it's not clear. I'm trying to do simple without too much information but sometimes is difficult.

Where is the formula?

Column H : SOMMEPROD(--(1+MOD((COLONNE(K20:CC20)-COLONNE(K20));9)=1)*(K20:CC20>0)*N(+K20:CC20))

Column G : SOMMEPROD(--(1+MOD((COLONNE(L20:CC20)-COLONNE(L20));9)=1)*(L20:CC20<0)*N(+L20:CC20))

1. Ignore. My original Point 1 was incorrect!!!

2. I have no idea what the expected answers are meant to be as you didn't provide any.

3. HOWEVER... I think the problem is that you started the formula in the incorrect column.

It originally read:

=SUMPRODUCT(--(1+MOD((COLUMN(K20:CD20)-COLUMN(K20)),9)=1)*(K20:CD20>0)*N(+K20:CD20))

whereas (I think) it should have read:

=SUMPRODUCT(--(1+MOD((COLUMN(L20:CD20)-COLUMN(L20)),9)=1)*(L20:CD20>0)*N(+L20:CD20))

but I'm only guessing, as there were no expected answers.

At the begining the formula at the begining was the same for columns H and G

=SUMPRODUCT(--(1+MOD((COLUMN(L20:CD20)-COLUMN(L20)),9)=1)*(L20:CD20>0)*N(+L20:CD20))

I changed it to

Column H : SUMPRODUCT(--(1+MOD((COLUMN(L20:CD20)-COLUMN(L20)),9)=1)*(L20:CD20>0)*N(+L20:CD20))
Column G : SUMPRODUCT(--(1+MOD((COLUMN(L20:CD20)-COLUMN(L20)),9)=1)*(L20:CD20<0)*N(+L20:CD20))

cause I needed to make appear some values in another sheet. It's still working and it turns what I expcted every time when every third column (in my example the L, U, AD, etc.) has only negatifs or only positifs values.


If K20 and T20 are > 0 then I have the result in H20 > 0 which = OK

L20 and U20 could be:

1. both < 0 and I'll have a value < 0 in G20 (the addition of all <0 values in the other columns if not blanks) = OK

Then in D20 I have the difference between G20 and H20 = OK

BUT

if ever I have negatifs AND positif values in a third column somewhere (L, U, AD, etc) in G20 I only see the sum of all negatifs values without consedering the positifs. For instance If I have G20 = -6.249 (cause L20=-6.249) and U20 =2 I need to make excel understand that he has to do the substraction of 2 from -6.249 so the result in G20= -4.249


The expected result is

I would like to keep this configuration :

Column H : SUMPRODUCT(--(1+MOD((COLUMN(L20:CD20)-COLUMN(L20)),9)=1)*(L20:CD20>0)*N(+L20:CD20))
Column G : SUMPRODUCT(--(1+MOD((COLUMN(L20:CD20)-COLUMN(L20)),9)=1)*(L20:CD20<0)*N(+L20:CD20))

1 case:

L18=22
U18= 10

then
L20 = -6.249 and U20 = -7 then G20 = - 13.249
K20 =3.751 and T20=3 then H20 = 6.751

then D20 = -6.50

2 case

L18= 22
U18= 40

then
L20 = -6.249 and U20 = 2 then G20 = - 6.249 (not ok cause I need to substract the value of U20 if >0 from 6.249 so = -4.249)
K20 =3.751 and T20=12 then H20 = 15.751

then D20 = 11.50 (and not 9.50)

Hope is more clear, it's not always easy to explain since I don't explain the purpose of my file which would be too long and complicated for you all here. I know you are busy and you need to know the essential in order to help the others. so I try to do it as simple as I can but sometimes is not very clear, I agree and I'm sorry about.

Administrative Note re. Forum Guideline #2:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter and rarely necessary.

If you are responding out of sequence, it is usually enough just to mention the helper's user name (e.g @AliGW).

If you do need to quote, limit the quoted section just to the section to which you wish to draw your helper's attention or a direct question to which you wish to respond.

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

Provide an updated sample workbook showing the problem and with expected answers mocked up manually.

WHERE in the workbook are the MOCKED-UP expected results? I don't think it is at all clear, actually. If it were, this would all have been resolved by now.