Sum up formula

adamsm

Active Member
Joined
Apr 20, 2010
Messages
444
Hi anyone,

How could I write a sum up formula that would apply for the following purpose. Suppose;

The user types January in cell AA9 and 2011 in cell AH9; I want the formula to copy all the fruits that has been sold to all the "OUT" customers (from column L of the sheet "Mysheet") to get copied to the cells of the steet "stat" starting from "D11" to AI11 and fruits sold to all the IN customers to get copied to the cells starting from "D12" to "AI12" of the same sheet.

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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Cell D11 on Stat Sheet should =

=SUMPRODUCT(--(MONTH(DATEVALUE(TEXT(MySheet!$AB$16&" "&1&", "&MySheet!$AH$16,"mm/dd/yy")))=MONTH(MySheet!$G$17:$G$39)*((MySheet!$F$17:$F$39)=VALUE(Stat!D$10))*((MySheet!$L$17:$L$39)=Stat!$C11)))

Not sure if you were looking to match it to the day or the 1 2 3 4...23 in column (f17:f39.

Copy this formula down to d12 and across.

Also, you have 2x 21's on your STAT spreadsheet. So you should remove one as this will double count entries in 21 and give you bad totals.
 
Last edited:
Upvote 0
Thanks for the help. But there seems to be a concern with the formula.

What I'm trying to get is suppose the user writes January in cell AA9 and year in cell AH9; I want all the OUT customers for day 1 to get copied to cell D11 and all the IN customers for day 1 of January to get copied to cell D12.

I want the same to happen to January 1,2,3,4 and so on until 31st.

Was the provided formula meant to do so? I tried it but am unable to succeed.

Any further suggestions on this would be kindly appreciated.

Thanks in advance.
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top