Sumif

[iyervsv]

Dear Mr Chandoo


I have a problem. I have two columns viz., challan date and amount as under.... the data I have to analyse is for one or two years. I want to sum monthwise for the challans and sometimes i want to sum along with customer code. Can you please explain me use of SUMIF and SUMPRODUCT in your easy way to enable me understand it comfortably.


Thanks in advance


ChallanDate Amount CustomerCode

20/07/2011 45867 AGI001

20/07/2011 47435 AGI120

20/07/2011 49003 APA567

21/07/2011 50571 AXP675

21/07/2011 52139 VXT156

21/07/2011 53707 AVC546

22/07/2011 55275 AGI001

22/07/2011 56843 AGI120

23/07/2011 58411 APA567

23/07/2011 59979 AXP675

23/07/2011 61547 VXT156

23/07/2011 63115 AVC546

23/07/2011 64683 AGI001

25/07/2011 66251 AGI120

25/07/2011 67819 APA567

25/07/2011 69387 AXP675

25/07/2011 70955 VXT156

25/07/2011 72523 AVC546

26/07/2011 74091 AGI001

26/07/2011 75659 AGI120

26/07/2011 77227 APA567

26/07/2011 78795 AXP675

27/07/2011 80363 VXT156

27/07/2011 81931 AVC546

27/07/2011 83499 XYP367

27/07/2011 85067 PYT215

27/07/2011 86635 TXV165

29/07/2011 88203 XYP367

29/07/2011 89771 PYT215

29/07/2011 91339 TXV165

30/07/2011 92907 XYP367

30/07/2011 94475 PYT215

30/07/2011 96043 TXV165

30/07/2011 97611 AGI001

30/07/2011 99179 AGI120

30/07/2011 100747 APA567


V S Venkatraman

 

[Hui]

Using SUMIFS


To Sum between 2 dates

=SUMIFS(Amount,ChallanDate,">="&DATE(2011,7,10),ChallanDate,"<="&DATE(2011,7,20))


To Sum between 2 dates where Customer = AGI001

=SUMIFS(Amount,ChallanDate,">="&DATE(2011,7,10),ChallanDate,"<="&DATE(2011,7,20),CustomerCode,"AGI001")


Using SUMPRODUCT


To Sum between 2 dates

=SUMPRODUCT((ChallanDate>=DATE(2011,7,10))*(ChallanDate<=DATE(2011,7,20)),Amount)


To Sum between 2 dates where Customer = AGI001

=SUMPRODUCT((ChallanDate>=DATE(2011,7,10))*(ChallanDate<=DATE(2011,7,20))*(CustomerCode="AGI001"),Amount)


I Have assumed you have Named Formula for the 3 Columns, ChallanDate, Amount & CustomerCode

 

[iyervsv]

Dear Mr Hui


Thanks a ton...this has saved my time a lot...


I'll be greatful, if you could explain me the * placed in sumproduct function..


Thanks in advance


V S Venkatraman

 

[Hui]

as used in sumproduct * means Multiply

each part of Sumproduct wuill result in an array of values

so:

ChallanDate>=DATE(2011,7,10) will result in an array of 0's and 1's for False/True

also

ChallanDate<=DATE(2011,7,20) will result in an array of 0's and 1's for False/True

and so multiplying them together will return a value of 1 only where the two arrays are both 1 in the same position


This array is then multiplied by the Amount array

to give you a list of Amounts that match the criteria


Sumproduct then adds them up.