PDA

View Full Version : Sum up formula



adamsm
06-09-2011, 12:27 PM
Hi,

How could I write a formula that would total the items in column "D"; related to vegetables of the sheet "Order" in sheet "New" starting from column D to AI; when the user writes the month in cell AA and year in cell AH.

Lets say for example; the user writes January in cell AA and 2011 in cell AH.

I want the total of all the vegetables sold to OUT & IN customers on the date 2/1/2011 to get sum up in cell D11 & D12.

I hope I've made my question clear.

Any help on this would be kindly appreciated.

Thanks in advance.

Bob Phillips
06-09-2011, 01:11 PM
Try

=SUMIFS(Order!$G:$G,Order!$D:$D,"Vgetables",Order!$A:$A,DATEVALUE(D$10&"-"&$AA$1&"-"&$AH$1))

Bob Phillips
06-09-2011, 01:12 PM
Oops, missed the OUT/IN

=SUMIFS(Order!$G:$G,Order!$D:$D,"Vgetables",Order!$A:$A,DATEVALUE(D$10&"-"&$AA$1&"-"&$AH$1),Order!$I:$I,$C11)

adamsm
06-09-2011, 03:53 PM
Thanks for the help xld. Your formula works the way I had suggested and I do really appreciate that.

If I may ask further help; I have another sheet in the same workbook with the name "Me"

What im trying to get is a formula that would sum up the rows same as with the date of the customer location for the category vegetables.

Suppose if I write jan and OUT in cell C11 I want all the vegetables to get sum up to the same row column D11 with date, and so on for the IN customer location.

Any help on this would be kindly appreciated.

I tried modifying your formula. But failed.

Bob Phillips
06-10-2011, 12:00 AM
Is this what you want, assuming Jan in B11, Out in C11

=SUMIFS($I:$I,$J:$J,"Vegetables",$G:$G,">="&DATEVALUE("01-"&$B$11&"-2011"),$L:$L,$C$11)
-SUMIFS($I:$I,$J:$J,"Vegetables",$G:$G,">"&EOMONTH(DATEVALUE("01-"&B11&"-2011"),0),$L:$L,$C$11)

adamsm
06-10-2011, 01:36 AM
Thanks for the reply xld, but it's not exactly what I meant. What I want is suppose,

The user types January in cell AA9 and 2011 in cell AH9; I want the formula to copy all the vegetables that has been sold to all the OUT customers to get copied to the cells starting from D11 to AI11 and vegetables sold to all the IN customers to get copied to the cells starting from D12 to AI12.

Meaning on daily basis.

And when the user selects another month like February or any other month, all the data related to February to get copied to the above mentioned cells.

I hope I've made my question clear.

Any help would be kindly appreciated.