Count with sumproduct

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
Ok I know I've done these before, but I must be off in Neverland.
US Foodservice SR Final 12-14-05.xls
ABCDEFGH
1Acct SiteWeight or
2DateSeq TruckCpty Ld%QuantityUomAccountLocationGroup
306/032 1 6000177.75TN22350000043
406/033 1 6000173.43TN22350000041
506/106 1 6000219.07TN22350000043
606/111 1 6900262.31TN22350000041
706/171 1 6670585.49TN22350000043
806/172 1 6670588.41TN22350000041
906/242 1 6000217.26TN22350000043
1006/247 1 6000214.21TN22350000041
1107/012 1 6000216.46TN22350000043
1207/013 1 6000212.6TN22350000041
1307/082 1 6000215.81TN22350000043
1407/083 1 6000212.93TN22350000041
1507/151 1 6000177.57TN22350000043
1607/152 1 6000175.18TN22350000041
1707/223 1 6000193.04TN22350000041
1807/224 1 6000197.32TN22350000043
1907/296 1 6000193.08TN22350000041
2007/297 1 6000198.76TN22350000043
2108/054 1 6892392.43TN22350000041
2208/055 1 6892398.22TN22350000043
2308/126 1 6081627.96TN22350000043
2408/127 1 6081624.03TN22350000041
2508/192 1 6000218.54TN22350000043
2608/193 1 6000213.26TN22350000041
2708/261 1 6900263.36TN22350000043
2808/262 1 6900269.82TN22350000041
2909/015 1 6000217.42TN22350000041
Sheet1


=SUMPRODUCT((MONTH(Sheet1!$A$3:$A$54)=6)*(Sheet1!$F$3:$F$54=Tonnage!D30*1)*(Sheet1!$H$3:$H$54=RIGHT(Tonnage!E30,1)))

Need all the months = 6
account to be a number so *1
look at the group, mine has combined info so take the right 1 character

What am I missing?
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,988
Hi, texasalynn.

Are the values in column H text or numbers? You may need RIGHT(Tonnage!E30,1)*1 in your formula if they're numbers.
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
THANKS Oaktree! that was it, knew I must be staring at it.

Have a wonderful Christmas
 

Watch MrExcel Video

Forum statistics

Threads
1,118,670
Messages
5,573,569
Members
412,537
Latest member
Mohamed_5966
Top