SUMPRODUCT with in date range

[wladoD]

Hi Guys,

How are you.

I would like to ask you, if you could help me.
I have sum-product formula but strangling to add date range. for example from Jan to Feb or Jan to March etc.

I have attached my excel sheet screen shot.

Big Thanks for your help.
Vlado

[oeldere]

Please add a small excel file on the forum.

Most forummembers are not interested in retyping your data, just to help you.

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually). It is far from clear what result(s) you are hoping to see!!

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[wladoD]

Hi,

Thanks for explaining.

I have a table of 10 items with different category.
For example I would like to sum all same of 'starter' category from Jan 17 to Mar 17.
Example:
Starter Jan 17 to March 17 - 101
Starter Jan 17 to Feb 17 - 69
Starter Jan 17 to Jan 17 - 32

Challenge is vertical and horizontal sumifs.

I have attached small excel sheet.

Thank you

[Glenn Kennedy]

Like this???

=SUMPRODUCT((category=$B$16)*(G2:R2>=$C$15)*(G2:R2<=D15)*G3:R12)

[wladoD]

Thank you Glenn

[Glenn Kennedy]

I see that you're in Bratislava. I spent a few very good days there, two years ago. Great food!!!

Anyhow; you're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[wladoD]

Yes, I am originally born there.
I have been travelling for couple of years, at the moment living in Thailand.

I have few more questions if I may.

In column E and F i have cost & selling price per item.
Is it possible sum-product can calculate it for me without me creating extra column where I have to calculated.

Example;

I ll use dessert as I know you prefer it

Total Cost Dessert Jan 17 to Feb 17 - 7415
Total Revenue Dessert Jan 17 to Feb 17 -16655

I have added in the sample

[Glenn Kennedy]

Lucky you... I'm often in Thailand. I don't like Irish winters... We now spend 2 months a year in SE Asia. Currently in NE Spain!

Are you based in BKK?

I'll look at your Q in a moment...

[Glenn Kennedy]

Use this, copied across:

=SUMPRODUCT(($C$3:$C$12=$B$16)*(E$3:E$12)*($G$2:$R$2>=$C$15)*($G$2:$R$2<=$D$15)*$G$3:$R$12)

[wladoD]

Hi,

Great, I think now I have better understanding of sumproduct.

Yes, I have lived in India, Vietnam and Thailand in last 4 years.
Nope, I am in Hua Hin near the sea.

Big Thanks

Have a nice day

[Glenn Kennedy]

We were in guesthouse, just off Soi 80 for most of February!! small world...

[wladoD]

Well next time you are around. I ll buy you few cold Changs

Just wondering. Is it possible with sumproduct to get total of all items sales in all categories?

Thanks

[JohnTopley]

Try

=SUMPRODUCT((E$3:E$12)*($G$2:$R$2>=$C$15)*($G$2:$R$2<=$D$15)*$G$3:$R$12)

For January (selected dates)

[wladoD]

Thank you very much.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.