sumif multiple criteria are met

[Vanali]

i have an inventory tracking sheet that has the same item in multiple rows. i am trying to sumif the duplicate items that also meet the criteria of shipped or not shipped. i already have a column with a formula that states shipped or not shipped based on the date.

the identifying style is on column A, the quantity is on column J and the status of shipped / not shipped on column L.

would you be able to help with this?

 

[DanteAmor]

You can use a pivot table to get totals by Item and by status.

 

[Vanali]

I can’t use a pivot table. I am trying to move the information to another summary tab. I tried using Vlookup, but Vlookup won’t add if there are multiple entries of the same item.

 

[t0azt]

SUMIFS will do that.Given Dante's example to get item A shipped total the formula would be

=SUMIFS(J2:J11,A2:A11,"A",L2:L11,"shipped")

 

[DanteAmor]

I can’t use a pivot table. I am trying to move the information to another summary tab. I tried using Vlookup, but Vlookup won’t add if there are multiple entries of the same item.

You can describe how you have the data in the summary sheet.

If you have something like this in the summary

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>Summary Sheet</b></td></tr></table><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">ITEM</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">shipped</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">not shipped</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >A</td><td style="text-align:right; ">202</td><td style="text-align:right; ">204</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >B</td><td style="text-align:right; ">210</td><td style="text-align:right; ">212</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >C</td><td style="text-align:right; ">108</td><td style="text-align:right; ">109</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=SUMIFS(Sheet1!$J$2:$J$11,Sheet1!$A$2:$A$11,$A2,Sheet1!$L$2:$L$11,B$1)</td></tr></table></td></tr></table>


Drag the formula to the other cells

 

[Vanali]

Hi all - thank you so much for the feedback.

for some reason that didn't work.

Maybe i am not doing a good job explaining it. I have two tabs. Tab one is called master the second tab staged.

On the master tab i have the identifying style on column A that also matches the Staged tab on column A as well. then i also have a column on the master tab called Staged and another called Shipped. I want the information to move to these two columns (staged and shipped).

on the Staged tab. on column J, i have the staged quantity, then on column L i have a formula that tells me if the row is shipped or not shipped based on the date. (the shipped and not shipped is not in separate columns) on column K i have the dates.

ultimately, i would like to move this info from the staged tab to the master sheet. i want the quantities on the rows that say shipped to go onto the shipped column in the master tab and the rows identified as not shipped to go under the staged column in the master tab. (the staged tab has multiple entries of the same item under different shipdate, I want to make sure these multiple entries get added when transferred to the new sheet)

I hope I did a better job his time explaining this.

thanks

 

[DanteAmor]

It would be better expressed if you could give examples of what you have on each sheet. Envelopes that data you make an explanation of what you want to move from the "staged" sheet to the "master" sheet.

Upload one image before the process and another image after the process.

Or upload a couple of files with before the process and after the process.

It is important that you show your data before the process and what you expect to result after the process, because I am not understanding if you want a sum or a count or all the records.

If you put examples, it would be much easier.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[Vanali]

Dante,

thanks for your patience, here is the link of the file with generic data https://www.dropbox.com/s/xx5ubugdak6y427/inventory sample.xlsx?dl=0

i am trying to sum the duplicate styles on the staged tab when they move to the master tab

 

[DanteAmor]

You did not explain.
Nor did you put the before and the after.