• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to add the Start Date and End Date in Sumproduct Formula

sgmpatnaik

sgmpatnaik

Active Member
Hello

i have small problem that is i want to add the Start Date and End Date in a Sumproduct Formula as a extra Critera

my formula is

Code: 
=SUMPRODUCT(('Dealer List'!$C$6:$C$50=$C9)*('Dealer List'!$H$5:$I$5=F$7),'Dealer List'!$H$6:$I$50)+SUMPRODUCT(('Dispatch Register'!$F$6:$F$3490=$C9)*('Dispatch Register'!$O$6:$O$3490=F$7),'Dispatch Register'!$N$6:$N$3490)-SUMPRODUCT(('Dispatch Register'!$F$6:$F$3490=$C9)*('Dispatch Register'!$O$6:$O$3490=F$7),'Dispatch Register'!$L$6:$L$3490)

The Date Columns in Dispatch Register is Column C and start date and End date column Range is H5

Kindly suggest me

Thanks
 
Hi Sgmpatnaik,

Can you upload a sample file with some data, and what the result you expect and where in formula you want the date criteria?

Somendra.
 
Hi Patnaik ,

The formula you have posted , has 3 sections :

SUMPRODUCT(('Dealer List'!$C$6:$C$50=$C9)*('Dealer List'!$H$5:$I$5=F$7),'Dealer List'!$H$6:$I$50)

SUMPRODUCT(('Dispatch Register'!$F$6:$F$3490=$C9)*('Dispatch Register'!$O$6:$O$3490=F$7),'Dispatch Register'!$N$6:$N$3490)

SUMPRODUCT(('Dispatch Register'!$F$6:$F$3490=$C9)*('Dispatch Register'!$O$6:$O$3490=F$7),'Dispatch Register'!$L$6:$L$3490)

The first section does not involve data on the Dispatch Register tab ; the second and third sections do.

You need to say whether the date criteria need to be applied to both the second and the third sections , only the second , or only the third.

Be that as it may , the formula will be extended by including :

(('Dispatch Register'!$C$6:$C$3490>=$H$5)*('Dispatch Register'!$C$6:$C$3490<=$H$6)

I am assuming that the Start Date is in $H$5 , and the End Date is in $H$6.

Narayan
 
@NARAYANK991

Thanks for your valuable replay, yes i forget to mention the End Date Column Sorry for that
now coming to point about my question is that

In List Tab i have mention the Party Named in the Column C and In Column H5 & I5 i mention the Brand Name, from H6 to I50 i mention the Party Opening balance, for this part the i used as

SUMPRODUCT(('Dealer List'!$C$6:$C$50=$C9)*('Dealer List'!$H$5:$I$5=F$7),'Dealer List'!$H$6:$I$50)

In Dispatch Register Sheet i have mention the Party Names in Column F , in Column O i Mention the Brand and Column N i mention the Quantity, for this i use

SUMPRODUCT(('Dispatch Register'!$F$6:$F$3490=$C9)*('Dispatch Register'!$O$6:$O$3490=F$7),'Dispatch Register'!$N$6:$N$3490)

so as per your instruction i add the extra line in last please check this is it correct or not

Code: 
=SUMPRODUCT(('Dealer List'!$C$6:$C$50=$C9)*('Dealer List'!$H$5:$I$5=D$7),'Dealer List'!$H$6:$I$50)+SUMPRODUCT(('Dispatch Register'!$F$6:$F$3490=$C9)*('Dispatch Register'!$O$6:$O$3490=D$7),'Dispatch Register'!$N$6:$N$3490)*('Dispatch Register'!$C$6:$C$3490>=$C$5)*('Dispatch Register'!$C$6:$C$3490<=$H$5)-SUMPRODUCT(('Dispatch Register'!$F$6:$F$3490=$C9)*('Dispatch Register'!$O$6:$O$3490=D$7),'Dispatch Register'!$L$6:$L$3490)*('Dispatch Register'!$C$6:$C$3490>=$C$5)*('Dispatch Register'!$C$6:$C$3490<=$H$5)

Thanking you

With Regards
 
Hi Patnaik,
Just curious to see your sample file.. :)
  • C$6:$C$50=$C9
    • Its Intersecting the range.
  • $H$5:$I$5=F$7
    • No of Array in all other range, are not same.
 
@deb Bhai

in the List Tab i have mention the Party names in C6:c50
and In H5 = ABC AND I5 = XYZ and from H6 to I50 i have given the Quantity of Opening Balance

Say
Code: 
H5 - ABC                I5 - XYZ
10                            5
-5                           15

Hope it clear

Thanks
 
@Sgmpatmaik
If you consider this much portion only of the formula =SUMPRODUCT(('Dealer List'!$C$6:$C$50=$C9)*('Dealer List'!$H$5:$I$5=D$7),'Dealer List'!$H$6:$I$50) will produce value error because array dimensions are different.

If you explain in file what are the condition you want and what you want to add and multiply than we can derive formula.

Regards!
 
Hi patanaik,

I am leaving 1st part.. (calculations from List Sheet... I learned a new thing.. its working fine.. Thanks) with you..
and for second part.. (Do you have office 2007+) try this...

=SUMPRODUCT((List!$B$6:$B$17=$B7)*(List!$G$5:$N$5=$C6),List!$G$6:$N$17)+SUMIFS('Dispatch Register'!K:K,'Dispatch Register'!D:D,$B7,'Dispatch Register'!L:L,C$6,'Dispatch Register'!B:B,">="&$C$5,'Dispatch Register'!B:B,"<="&$H$5)-SUMIFS('Dispatch Register'!I:I,'Dispatch Register'!D:D,$B7,'Dispatch Register'!L:L,C$6,'Dispatch Register'!B:B,">="&$C$5,'Dispatch Register'!B:B,"<="&$H$5)

In case of 2007- Excel..

Try this..

=SUMPRODUCT((List!$B$6:$B$17=$B7)*(List!$G$5:$N$5=$C6),List!$G$6:$N$17)+SUMPRODUCT('Dispatch Register'!K6:K17,('Dispatch Register'!D6:D17=$B7)*('Dispatch Register'!L6:L17=C$6)*('Dispatch Register'!B6:B17>=$C$5)*('Dispatch Register'!B6:B17<=$H$5))-SUMPRODUCT('Dispatch Register'!I6:I17,('Dispatch Register'!D6:D17=$B7)*('Dispatch Register'!L6:L17=C$6)*('Dispatch Register'!B6:B17>=$C$5)*('Dispatch Register'!B6:B17<=$H$5))
 
@Somendra

Please Check my sample file which is i attached in my previous post

if any issue please inform me

Thanks
 
@deb Bhai

Thanks for Your kind words

and i am using Office 2007

coming to your point sorry to say this your formula is not work i think you didn't check the formula in my file in BP & GP Tab

it's getting the result from only List sheet of Opening Balance

if possible please upload the sample file

Thanking you
 
yes.. Becouse.. Of date range.. From dispatch sheet..
they all are from 2012..
check with any valid date range..
 
@deb Bhai

ooops :oops: ! i miss that part sorry

i think now i have to move to take some drink

Thanks and sorry again for my mistake, Let me check that part if any issue i will inform

Thanking You

Cheers
 
@sgmpatnaik

Now I had used this formula. See the attached file and compare the result with the expected. If it is right than you can modify the references for other cells also.

Code: 
=SUMPRODUCT(--(List!$B$6:$B$15=$B7),List!$G$6:$G$15)+SUMPRODUCT(('Dispatch Register'!$D$6:$D$15=$B7)*('Dispatch Register'!$L$6:$L$15=$C$6)*('Dispatch Register'!$B$6:$B$15>=$C$5)*('Dispatch Register'!$B$6:$B$15<=$H$5),'Dispatch Register'!$K$6:$K$15)-SUMPRODUCT(($B7='Dispatch Register'!$D$6:$D$15)*('Dispatch Register'!$L$6:$L$15=$C$6)*('Dispatch Register'!$B$6:$B$15>=$C$5)*('Dispatch Register'!$B$6:$B$15<=$H$5),'Dispatch Register'!$I$6:$I$15)

Regards!
 

Attachments

Hello Patnaik,

Another way:

=SUMIF(List!$B:$B,$B7,INDEX(List!$G:$N,,MATCH(C$6,List!$G$5:$N$5,0)))+SUM(SUMIFS(OFFSET('Dispatch Register'!$K:$K,,{0,-2}),'Dispatch Register'!$D:$D,$B7,'Dispatch Register'!$L:$L,C$6,'Dispatch Register'!$B:$B,">="&$C$5,'Dispatch Register'!$B:$B,"<="&$H$5)*{1,-1})
 
You must log in or register to reply here.
Back
Top