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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

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,112,885
Messages
5,543,038
Members
410,583
Latest member
gazz57
Top