# Help needed with formula for Date Range

#### zookeeperbobbie

##### Board Regular
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

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
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
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
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
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

Replies
9
Views
117
Replies
6
Views
105
Replies
3
Views
182
Replies
5
Views
135
Replies
5
Views
53