Help needed with formula for Date Range

zookeeperbobbie

Board Regular
Joined
Feb 9, 2005
Messages
117
Hello, I have a formula that I am stuck with
I need to create a formula that will give me the total # of pcs per vendor in a date range that I can specify.

So far this is the formula that I am using:

=SUMIF(MAIN!1:65536,C3,MAIN!K:K)

c3 is where i am typing in the vendor name column k is the row where the # pcs are entered

I tried using this formula but it will not work on sumif-If keeps changing the +0 to *0 and giving me a value error. It did work on a sumproduct that I tried to do...
=SUMPRODUCT((MAIN!$A$5:$A$310=$C$3)+0,(MAIN!F$5:F$310=$B15)+0,(MAIN!$C$5:$C$310>=$E$3)+0,(MAIN!$C$5:$C$310<=$E$5)+0)

(e3 is the start date range and e5 is the end date range- C5-310 IS Where that date is entered in my main database)
Any suggestions?
Thanks
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

zookeeperbobbie

Board Regular
Joined
Feb 9, 2005
Messages
117
Hi also have tried this but no luck...

=SUMPRODUCT((MAIN!K5:K1000>=100)+0,(A5:A1000="C3")+0,(K5:K1000))

I really really hope someone out there knows the answer. This is the last part to a very very horrible project i have had to create..

I can email this file if that will help-
Thanks
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Your formulas look very different. You said you wanted to get a total # of pcs per vendor in a date range

This one looks closest.....

=SUMPRODUCT((MAIN!$A$5:$A$310=$C$3)+0,(MAIN!F$5:F$310=$B15)+0,(MAIN!$C$5:$C$310>=$E$3)+0,(MAIN!$C$5:$C$310<=$E$5)+0)

but what's in F5:F310? If the number of PCs is in column K as you stated

=SUMPRODUCT((MAIN!$A$5:$A$310=$C$3)+0,(MAIN!$C$5:$C$310>=$E$3)+0,(MAIN!$C$5:$C$310<=$E$5)+0,MAIN!$K$5:$K$310)
 

zookeeperbobbie

Board Regular
Joined
Feb 9, 2005
Messages
117
The f5:f310 is from another formula that did work.. Sorry about that..

Like I said I have tried every kind of formula can think of to get this to work.. So thats why these are different...

thanks
 

zookeeperbobbie

Board Regular
Joined
Feb 9, 2005
Messages
117
I fiqured it out- the correct formula was :

SUMPRODUCT((MAIN!$A$5:$A$310=$C$3)+0,(MAIN!$C$5:$C$310>=$E$3)+0,(MAIN!$C$5:$C$310<=$E$5)+0,MAIN!M$5:M$310)


Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,118,862
Messages
5,574,713
Members
412,613
Latest member
EFRATA
Top