Determine total (sum) of individual items in 16 column x 200 row sheet

[Randy Birch]

OK this might take some 'splainin' so ...

I sell things online.  Over 100 different things.

I have a spreadsheet that tracks sales … Jane Doe, total sale, and a breakdown of what she bought by entering the quantity in AJ and the name of the item from a fixed list in AK.  Ditto again AL (quantity) AM (item) AN (quantity) AO (item) and so on through AX/AY. Only reason I stopped at AX/AY was the formula was getting nuts. 

At the top of the page is a running total of how many of each item is in the sale area.  So if I had a sale and recorded 100 in AJ and "7.5" frame" in AK, this total would be summed and displayed elsewhere on the sheet.  If the next person bought 33 8" frames (AJ and AK) and 42 7.5" frames (AL and AM), the 7.5" frame total would update to 142 and the 8" frame total would move from 0 to 33.  Hope this is clear.

Right now I am using SUMIF, but with this it seems I need one SUMIF statement for each set of numbers (items sold) and item description), and with 8 columns of numbers and item descriptions, the formula is nuts... 

=SUMIFS(AJ$15:AJ$202,AJ$15:AJ$202,">0",AK$15:AK$202,BF1)+SUMIFS(AL$15:AL$202,AL$15:AL$202,">0",AM$15:AM$202,BF1)+SUMIFS(AN$15:AN$202,AN$15:AN$202,">0",AO$15:AO$202,BF1)+SUMIFS(AP$15:AP$202,AP$15:AP$202,">0",AQ$15:AQ$202,BF1)+SUMIFS(AR$15:AR$202,AR$15:AR$202,">0",AS$15:AS$202,BF1)+SUMIFS(AT$15:AT$202,AT$15:AT$202,">0",AU$15:AU$202,BF1)+SUMIFS(AV$15:AV$202,AV$15:AV$202,">0",AW$15:AW$202,BF1)+SUMIFS(AX$15:AX$202,AX$15:AX$202,">0",AY$15:AY$202,BF1)

… essentially saying, if anything in column AJ from cell 15 to 202 is more than zero, then if the text in the adjacent cell AK matches that in cell BF1, sum that puppy.  And so on for each set of adjacent columns through AX/AY.

Now, in my ideal world, I'd want one statement that said "look at everything in cells AJ5 to AY202 and if its a number and the text adjacent to it matches the text in BF1, sum it."

Is what I am asking possible?  Or is my convoluted SUMIF the best I am going to get? 

(I considered a pivot table but that seemed the wrong direction where the goal is summing the number if each individual item I sold. )

[#2]

Hello Birch,

It would be nice to attach a screenshot (if possible) so that we can see what you're trying to do.

[#3]

@Abiola1 

Suppose it would!  Rows 1 through 10 are total tally area.  Sales data starts on row 15.  Sales data is entered into columns AJ through AX.  (Cols A through AH are for accounting details pertaining to the sale).  The SUMIF formula is in cells AJ8-10, AL1-10, and so on.

[#4]

Randy, have you tried inserting a Pivot Table. If I am understanding your problem, then I feel sure a Pivot Table will fix it. Holler if you need help.

[#5]

@Randy Birch 

Hey Randy, try this.

Attached a small sample.

[#6 · Accepted solution]

@Randy Birch 

Another variant - sample

[#7]

That seems to work to total only the number of that item in the row.  My needs are more like the attached.  An item could occur anywhere and I need the total of all of each.

[#8]

Hi @Sergei Baklan 

That seems to work and is much more elegant … with a minor caveat : had to add one column to my sheet as MOD was failing when the first column of sales data is in AJ (the 36th column).  In your sample your formula was in an odd number column (9), so I have to either figure how to tweak MOD, or add one extra column somewhere before AJ to move its data to AK.

But that also points out the flaw in this approach .. any change to the number of columns I make subsequent to implementing this code will demand an even number of columns be added.  Any odd number causes the formula to error out.  Any suggestions around this issue?

=SUMPRODUCT( IF( MOD(COLUMN(AK$15:AY$202),2)=1,AK$15:AY$202,0)*IF( (MOD(COLUMN(AL$15:AZ$202),2)=0)*(AL$15:AZ$202=AL1),1,0 ))

EDIT:  seems I will just have to play around with the =1 and =0 values.  See that:

x=25
?x mod 2
 1
x=26
?x mod 2
 0

Flip them around in the equation and should work for being in an even column.  But the issue of adding new columns and having to ensure they don't muck up MOD is still a concern, or at least something I need to keep in mind in modifying the sheet.

Randy

[#9]

Hi Harry ….
I am sure a pivot table could be used, but my issue is the data in the sale area which is what is of interest to me, is comprised of only some of the items I have available. So while I may have a list of a hundred or so items, the area with the purchased data won't contain at least one of each upon which to build a pivot. What I need is one part of the pivot to reference all my possible items for sale, with another part referencing only those items sold, so I could see, for example,
brant frame 7.5 121
brnic frame 7.5 0
brgun frame 7.5 239
and so on. I've tried to get a pivot to simply add what is in the sold items area alone to no avail.

[#10]

@Randy Birch 

Randy, you may not to hardcode 1 or zero to compare the MOD(), but instead use MOD for the first/second column in range. For my sample it could be like

=SUMPRODUCT( IF( MOD(COLUMN(D1:J9),2)=MOD(COLUMN(D1),2),D1:J9,0)* (MOD(COLUMN(E1:K9),2)=MOD(COLUMN(E1),2))*(E1:K9="b"))

If you add the column before the range it shall work correctly, please see attached.