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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,835
Messages
5,855,907
Members
431,772
Latest member
dannyboi1

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