Counting Orders

lowranger69

New Member
Joined
Dec 1, 2017
Messages
6
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.
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

estevaoba

Board Regular
Joined
Dec 26, 2016
Messages
96
Assuming your range in A1:C1000 and month columns starting from E1 (jan/17), try this in E2, then copy across and down:

Code:
[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]
 
Last edited:

lowranger69

New Member
Joined
Dec 1, 2017
Messages
6
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

New Member
Joined
Dec 1, 2017
Messages
6

ADVERTISEMENT

DescPart #CostTotalJanFebMar
Widget A111111$10
Widget B222222$20
Widget C333333$30
Widget D444444$40

<tbody>
</tbody>
 

estevaoba

Board Regular
Joined
Dec 26, 2016
Messages
96
I made adustments. Try now.

Code:
[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]
 
Last edited:

lowranger69

New Member
Joined
Dec 1, 2017
Messages
6

ADVERTISEMENT

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

Board Regular
Joined
Dec 26, 2016
Messages
96
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

New Member
Joined
Dec 1, 2017
Messages
6
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

Board Regular
Joined
Dec 26, 2016
Messages
96
You can set excel Options menu not to show zero when result is 0.

Or you can use this:

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

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,611
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top