Counting Orders

[lowranger69]

Hello All,

I am trying to count orders placed from one sheet in a workbook to another sheet. I'm trying to do it by part number and month range.

For instance, I have sheet1 that I keep a running tab of orders placed; part #, date ordered and quantity. On sheet two, I have; description, part#, cost and would like to on the side keep track of monthly orders (count) of each individual part from sheet1.

So, obviously, if I order qty 2 1/25/18 and 3 1/28/17 on sheet1, I need it to reflect in Jan column that 5 were ordered.

 

[estevaoba]

Assuming your range in A1:C1000 and month columns starting from E1 (jan/17), try this in E2, then copy across and down:

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=SUMPRODUCT(($C$2:$C$1000)*($A$2:$A$1000=$A2)*(MONTH($B$2:$B$1000)=MONTH(E$1)))[/TD]
[/TR]
</tbody>[/TABLE]

 

[lowranger69]

Either I'm not completely understanding, or this doesn't reflect the data from a separate sheet?
Is there not a way to attach a spreadsheet?

 

[lowranger69]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Part#[/TD]
[TD]Date[/TD]
[TD]Qty[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Widget A[/TD]
[TD]1/25/18[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Widget C[/TD]
[TD]1/26/18[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Widget A[/TD]
[TD]1/28/18[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[lowranger69]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Desc[/TD]
[TD]Part #[/TD]
[TD]Cost[/TD]
[TD][/TD]
[TD]Total[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Widget A[/TD]
[TD]111111[/TD]
[TD]$10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Widget B[/TD]
[TD]222222[/TD]
[TD]$20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Widget C[/TD]
[TD]333333[/TD]
[TD]$30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Widget D[/TD]
[TD]444444[/TD]
[TD]$40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[estevaoba]

I made adustments. Try now.

[TABLE="width: 64"]
<tbody>[TR]
  [TD="width: 64"]=SUMPRODUCT((Sheet1!$C$2:$C$10)*(Sheet1!$A$2:$A$10=$B2)*(MONTH(Sheet1!$B$2:$B$10)=MONTH(E$1)))
[/TD]
[/TR]
</tbody>[/TABLE]

 

[lowranger69]

So, I have it edited, but it doesn't like the end =month.

I have the top of the column as Dec. It's giving me a value error. I added, sheet2!E$1, because it wasn't liking another part of the formula.

 

[estevaoba]

The part (MONTH(Sheet1!$B$2:$B$10)=MONTH(E$1)) requires month in E1 entered as Jan/17, Feb/17, etc.
You can then custom format those cells "mmm", to look like Jan, Feb, etc.

 

[lowranger69]

I got that to work! Wonderful. Thank you.

Now, I currently have a lot of zeros, how can I get it to leave it blank if the result is zero?

 

[estevaoba]

You can set excel Options menu not to show zero when result is 0.

Or you can use this:

[TABLE="width: 64"]
<tbody>[TR]
  [TD="width: 64"]=IF(SUMPRODUCT((Sheet1!$C$2:$C$10)*(Sheet1!$A$2:$A$10=$B2)*(MONTH(Sheet1!$B$2:$B$10)=MONTH(E$1)))=0,"",SUMPRODUCT((Sheet1!$C$2:$C$10)*(Sheet1!$A$2:$A$10=$B2)*(MONTH(Sheet1!$B$2:$B$10)=MONTH(E$1))))[/TD]
[/TR]
</tbody>[/TABLE]