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]

Part#	Date	Qty
Widget A	1/25/18	2
Widget C	1/26/18	10
Widget A	1/28/18	3

<tbody>
</tbody>

 

[lowranger69]

Desc	Part #	Cost		Total	Jan	Feb	Mar
Widget A	111111	$10
Widget B	222222	$20
Widget C	333333	$30
Widget D	444444	$40

<tbody>
</tbody>

 

[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]