• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

sum product clarification

V

vpxlquest

Member
SUMPRODUCT CLARIFICATION


A88 20

C999 10

C099 2

E888 12

E98 4


Sum product will be A = 20, C=12,E=16 = total 48

Lets say a user inputs values like this


A88 20

C999 10

C099

2

E888 12

E98

4


Sum Product is A= 20, C = 10, E = 12 total = 42

Is there any way I can capture the blank value to match the sum in both cases


Thanks for reading this post.
 
In your second example, are the 2 and 4 in first column or second column?

What exactly is the criteria/math operation? It looks like you're just summing up the second column.
 
Yes I am summing up the values

2 & 4 are in the second column but the reference i.e C099 and E98 are in the next line.


So when I do a =SUMPRODUCT(--(LEFT($C$12:$C$1000,1)="C"),($D$12:$D$1000))

So when I do a =SUMPRODUCT(--(LEFT($C$12:$C$1000,1)="E"),($D$12:$D$1000))

It sums up all the codes with C and E but leaves these 2 lines since they are blank and gives me the wrong sum.

Task hours

blank 2

C099

blank 4

E98


How to capture “” or blanks to ensure I get the accurate total. Thanks again for your inputs.
 
when you say C099 is in the next line,

Is there a space or a Alt Enter causing that?


As if that is the case the first character won't be a C or E


can you post your data somewhere ?
 
Hello Hui


this is the file


https://skydrive.live.com/?cid=1cd3b2f518c01d9a&group=0&sc=documents#!/edit.aspx/.Documents/Book1.xlsx?cid=1cd3b2f518c01d9a&sc=documents&nd=1


i hope you can open it. XLD solution gives cumulative summation and is not the desired result.
 
<iframe width="402" height="346" frameborder="0" scrolling="no" src="https://r.office.microsoft.com/r/rlidExcelEmbed?su=2077200618899905946&Fi=SD1CD3B2F518C01D9A!131&kip=1&wdAllowInteractivity=False&wdHideGridlines=True&wdHideHeaders=True&wdDownloadButton=True"></iframe>


not sure if this time attaching the file will work
 
This solves it for you

=SUMPRODUCT(--(LEFT($A$12:$A$1000,1)="A"),($B$12:$B$1000))+SUMPRODUCT(--(LEFT($A$12:$A$1000,1)="A"),($B$13:$B$1001))


Note that the second array in the second sumproduct is offset by 1 row


The - - is called a double unary

its effect in this case is to convert the Bolean operation of (LEFT($A$12:$A$1000,1)="A")

which will return an array of True/Falses to an array of 1's and 0's for True/False

these can then be multiplied by the second array.
 
You could also think about warning the staff about putting data in the correct spot by using Conditional Formatting.


Selact A12:A1000

apply a CF using an equation

=AND(A12<>"",B13<>"")

Apply a Format


Apply the CF
 
Hello Hui,


thanks for your input. The =SUMPRODUCT(--(LEFT($A$12:$A$1000,1)="A"),($B$12:$B$1000))+SUMPRODUCT(--(LEFT($A$12:$A$1000,1)="A"),($B$13:$B$1001))


Seems to have a cumulative total effect and does not give me the actual results.


For eg. if the sumproduct of A= 12

and if i apply this formula for C = 2

result is

A=14

C=2 and so on. How to ensure that once the values are derived for the A,B&C etc they do not change for others. is this a reference issue.


thanks again.
 
With your data I get A=4 as it should ?


Can you clarify what you mean by a Cumulative Effect?

as that is exactly what Sumproduct does


Did you change both Occurrences of A to B, C or D etc


You can link the Reference to another cell if required, like Cell D1 in the following


=SUMPRODUCT(--(LEFT($A$12:$A$1000,1)=D1),$B$12:$B$1000))+SUMPRODUCT(--(LEFT($A$12:$A$1000,1)=D1),($B$13:$B$1001))


Please also check that you have Calculation set to automatic
 
Ry this


=SUMPRODUCT(((LEFT($A$12:$A$200,1)="A")*$B$12:$B$200)+(($A$12:$A$200="")*(LEFT($A$11:$A$199,1)="A")*$B$12:$B$200))
 
Looks like I may have the concept wrong.

I have series of alphanumeric codes eg. A677,A987,A908,B897,B87,B990,C999,C766,F998

My intent is to sum all the occurrences of A,B,C,F and so on.

The sum product function that you had defined worked fine until I realized that a user was not inputting the code adjacent to the hours but above or below as see in my spreadsheet and the sum was not adding up since my formula was not taking the blank cells into account.

I went to Excel option formula and the automatic calculation is checked. Will sumif function work in this case. I want the sum of all A , B,C,F etc.

I do appreciate all your help out there but I am stumped as to why this is not working.

I tried XLD formula it had the same issues. I just checked that when totaling the last occurrence it gives inaccurate values.

Eg. Lets say the last code typed in is F8787 it gives a cumulative F value which does not add up to A+B+C etc. Is it to do with the blank cells. The range is set from A12:A1000 is it trying to read the blanks. A dumb question –is there any value for a blank excel cell. Also the hours are set to number with decimal 2 places.
 
If it is cumulative values you want from A..F? try

Code: 
=SUMPRODUCT((LEFT($A$12:$A$1000,1)>"@")*(LEFT($A$12:$A$1000,1)<=I1),($B$12:$B$1000))+ SUMPRODUCT((LEFT($A$12:$A$1000,1)>"@")*(LEFT($A$12:$A$1000,1)<=I1),($B$13:$B$1001))


where I1 has a lookup value eg: F

The above formula is 1 line
 
I found out the reason for this strange behavior. I have a autosum function at the end of the hour column.

Lets say the total sum of hour 26 and the last code value was 2 it gave me 28. The moment I deleted the autosum it gave me the results.

On further testing I find that if a user enters a task code as a new entry than the formula adds up correctly. But is he inserts a line inbetween 2 task code results are wrong.

F878 -1

G77-2

If I insert A234 = 3 inbetween F878 and G77 it give the sum of A234 = 3+ G77 = 5

A occurance = 5. Is this because all the blank value above is being totalled.

A user cannot insert a line inbetween to enter his task number am I correct.


is there any work around this. thanks again
 
ok xld solution

=SUMPRODUCT(((LEFT($A$12:$A$200,1)="A")*$B$12:$B$200)+(($A$12:$A$200="")*(LEFT($A$11:$A$199,1)="A")*$B$12:$B$200))


eliminated the problem and i can insert a new task inbetween 2 tasks. I really thank Hui and xld for sticking with me through all my rambling till i found out the solution. thanks once again appreciate your time, effort and dedication. I have eliminated the autosum entry and it now finds the occurances and autosums seperately.
 
Understanding these codes.

=SUMPRODUCT(((LEFT($C$12:$C$1000,1)="A")*$D$12:$D$1000)+(($C$12:$C$1000="")*(LEFT($C$11:$C$999,1)="A")*$D$12:$D$1000))

Xld code why do we have 1 cell less (999) than the set range of 1000

Hui code

=SUMPRODUCT(--(LEFT($C$12:$C$1000,1)="b"),($D$12:$D$1000))

Both the code accomplishes the same results but does not help with my problem of data capture if the user enters blank. I will have to resort to conditional formatting as hui suggested. What is the key difference between the above 2 formulas.


=SUMPRODUCT(--(LEFT($C$12:$C$1000,1)="A"),($D$12:$D$1000))+SUMPRODUCT(--(LEFT($C$12:$C$1000,1)="A"),($D$13:$D$1001))

This result adds the integer found below and gives me cumulative results. Eg. If I insert value for L=2 and if the value below is G990 = 3 it gives the value of L = 5
 
You don't have 1 cell less, it starts 1 cell earlier and ends one cell later. That is so that it can check 2 rows in the same set of tests.
 
I am bemused about what isn't working ?


I have provided a solution for you where it equals a value and a cummulative answer?

eg: answer = sum(C) and answer = sum(A to C)


Please have a look at your data with my formula's

Here: https://rapidshare.com/files/1288542397/VPXLQuestSumproduct.xlsx


Both solutions allow for a blank with the data on the next line, and in fact they both allow for the data on that line as well as the next line


Please explain under what circumstances they aren't working ?


Note if use a Value of C in G20 (Yellow) you also include the value for the first row as your title "Case-2" is within your data range and includes the first letter C and so it includes the value for A.
 
If you change A22 to say A123, you will see that your formula in returns 9, which is the 3+4 for D76 and D78, but also includes the 2 for A123. The OP never said every other row would be blank, just that there might be some interspersed blanks where the value was contained.
 
Thanx for clarification


It would be easiest to add Conditional Formatting that warns when there is no value in Column A when a value is entered in Column B
 
I have some problems with this formula.


=SUMPRODUCT(((('[excell sheet.xls]Spreadsheet1'!$C$4:$C$9995=$A1340)*('[excell sheet.xls]Spreadsheet1'!$D$4:$D$9995>=$B$1))+(('[excell sheet.xls]Spreadsheet1'!$C$4:$C$9995=$A1340+1)*('[excell sheet.xls]Spreadsheet1'!$D$4:$D$9995<$B$1)))*('[excell sheet.xls]Spreadsheet1'!$BB$4:$BB$9995=D$1),'[excell sheet.xls]Spreadsheet1'!$AH$4:$AH$9995)


The result should be that certain amount or items and added between certain dates and times and then the total is shown as an exact figure.


Let's say.


I had packed 2345 boves of apples between today 7 am and tomorrow 7 am, so this formula goes into an spreadsheet where the packer logs the boxes packed per hour and the formula counts daily production from 7 until 7.


If I got 5,051 boxes from yesterday, the formula counts 5,067. It is just off by a small number and it is always off by a small number, I can't get it working so I can get the right figure.


Can anyone find the error or any clue about how to change the formula or the function please


Thanks


David
 
Hi ,


Can you please indicate what the following cells will contain ?


B1 , A1340 and D1


What data is in the columns C , D , AH and BB ?


Narayan
 
You must log in or register to reply here.
Back
Top