How to total in array?

[kalpeshkansara]

NEED TOTAL OF QTY FROM [A]+ TO [C]

IN [C] HAVING A TO AK PRODUCTS NAMES WITH THEIR UNIQUE SERIAL NUMBERS FROM 1 TO 37.

NOW LEFT SIDE IN [A] OR HAVING SOLD ITEMS BY ONLY SERIAL NUMBERS AND WE NEED TO PUT FORMULA IN [C] FROM 1ST SERIAL NUMBER FROM SOLD ITEMS BY DATE WRITTEN IN [A] OR .

WE HAVE TO CHECK IN [A] THAT WHICH ITEM CODE HAVE SOLD IN QTY? THAT QTY OF TOTAL FROM [A] OR NEED AT [C].

PLS ASK IF STILL NOT UNDERSTAND THIS WORKSHEET OR METHOD TO ME ON MY WHATSAPP NUMBER 9327904275.

 

[Nutster]

Re: How to total in arrey?

1) Please do not post in ALL CAPS. It makes it look like you are yelling.
2) What numbers in sections [A] and need to be added? Is the same as the numbers in red on row 22?
3) What is the correlation between the items in [A] and with the codes in section [C]? Do the I.S. No column values in [A] and correspond to the SRNO values in [C]?

 

[kalpeshkansara]

Re: How to total in arrey?

Yes right, Thanks for All caps suggestion. I wrote because I liked in all caps. but I will take care @ next sure.

I.S.No. values are Items serial numbers which located in [C].
ie. Where 31 in I.S.No. is the product named AE in [C]. in I.S.No. anywhere if i put 31 and its qty. it must come by total of qty to [C] 31 in qty column.

like that same need all to set.

 

[Nutster]

Re: How to total in arrey?

Using the SUMIF function should do the job. In cell L2
in [C] put:

=SUMIF($A:$A,J2,$B:$B)+SUMIF($C:$C,J2,$D:$D)+SUMIF($E:$E,J2,$F:$F)+SUMIF($G:$G,J2,$H:$H)[Code]
Each SUMIF function is working on one group of columns (Sales/Addition) in each of section [A] or [B].  I put the first SR NO in J2.  This formula can then be copied into all the rest of the appropriate cells; the relative references (without $ signs) will be updated relative to its new position.  The 3 arguments to the SUMIF function are the range to fine the value in question (You have these in columns A, E, I, M).  The second argument is which value to look for in the the search range.  The last argument indicates where the numbers to be added are to be found.

 

[kalpeshkansara]

Re: How to total in arrey?

No sir its not working like that as I want. In this formula you taken every column in count. We need only that column I m writing below in deeply that what calculation need. And J2 is having date only that is not in need in this calculations.

in [C] having S4 where need total of sum from C1+K1.

If in B5 to B21 I put respective values as i shown in photo, I taken that from [C]'s Q column where is having serial number of products which is in [C]'s R column.
Now if I put there in B5 31 and its qty is 1, It means that V15 is having 31 serial number and its qty I put 1 because that product of 31 number has sold 1 qty in my shop.

If in B5 to B21 I put respective values as i shown in photo, I taken that from [C]'s Q column where is having serial number of products which is in [C]'s R column.
Now if I put there in (B6 14) its qty is 2 and (J8 11) its qty is 3, It means that Q17 is having 14 serial number and its qty I put 2 because that product of 14 number has sold 2 qty in my shop.

like that all sold items I am keeping track to manage stock weekly Monday to Saturday.
So need to know that how many products I sold in a week. so need total in [C] from [A] and only sales side not addition side.

I hope now you got it what i need.

 

[kalpeshkansara]

Re: How to total in arrey?

We have to calculate like as below.

If B column have serial number of any products and its qty in [A] and then need total qty of [A] + of sales side qty for respective items.
That items can sale again by same serial number by different date. Means in a week total of single items can sale again n again more than 1 time by different dates.

we have to collect its total in [C].

Thats all

 

[Nutster]

Re: How to total in arrey?

I built my test differently than you laid things out. I will try that again using the addressing you used in the original post and try again.
Starting in S4:

=SUMIF($B$5:$B$21, Q4, $C$5:$C$21) + SUMIF($F$5:$F$21, Q4, $G$5:$G$21) + SUMIF($J$5:$J$21, Q4, $K$5:$K$21) + SUMIF($N$5:$N$21, Q4, $O$5:$O$21)

Each SUMIF looks in the range specified in the first argument, looking for the value specified in the second argument, in this case in Q4 (1). It then adds the corresponding values in the matching range in the third argument. Add them all up to get the grand total for all for sections.

Because the ranges are absolute and the referenced value is using relative addressing, you can just copy into all the other appropriate cells in section [C].

 

[kalpeshkansara]

Re: How to total in arrey?

Yes this is working perfect
thanks a lot