Formula/Macro for adding values that meet certain conditions on another sheet

[NIKNIKNIK]

Hey, I'm trying to figure out a macro or formula or something that can get this going here. What I have is a spreadsheet to track closeout items. Everything in column B is the item sku. Column K is the date the item was listed online. Now the second sheet is a sheet with column A being sales dates, column B is quantity, and C is the sku of the item itself. What I'd like to be able to do is have the list on sheet 2 be checked against the item sku in column B, as well as the date in column K, and add up all sales where the sku on sheet 2 column C contains the sku from sheet 1 column B and is within a week of the date in sheet 1 column K. Ideally the returned result would be the total sales of that sku within a week period, from a list with random dates and random skus. Any help that could be provided would be greatly appreciated as this set up has a lot of moving parts so it's been difficult to determine what routes I need to take. I can provide any clarification necessary if needed. Thanks in advance!

[Pete_UK]

Have you looked at using SUMIF or SUMIFS?

Pete

[NIKNIKNIK]

Yes, I just can't put everything together where I don't get a #VALUE! result or the right result I want. I've tried many different variations that I've seen but they don't seem to be conducive to what exactly I need to do, currently I have =SUMIFS(Sheet2!C2:C33,B2,Sheet2!B2:B33) which is intended to check just the C column for matches with B2 on sheet 1 (gives me a #VALUE! error anyway) but I need a partial match (IE in the case of Sheet 1 B2 being 5586, being able to get the sum of values from Sheet 2 column B where Sheet 2 column C contains 5586, like TC - Blue 5586 L), and since I have yet to successfully implement that, I have not begun to attempt to incorporate the date conditions, which would further narrow the results by a date range limited to a week within the date value in the related K column of sheet 1.

[Pete_UK]

You can use the wildcard character with SUMIF for partial matches, but if you also want to check on dates you might as well use SUMIFS for now so that it will be easier to expand (slightly different syntax), like this:

=SUMIFS(Sheet2!B$2:B$33,Sheet2!C$2:C$33,"*"&B2&"*")

Hope this helps.

Pete

[NIKNIKNIK]

Thanks! That definitely does what I need and functions, outside of the date. How would you (or anyone) suggest adding the one week check against the date in K2?

[Pete_UK]

Where are the dates? (sheet, cell-range etc.)

Pete

[NIKNIKNIK]

The dates should be in column K on sheet 1 and compared against dates in column A on sheet 2

[Deitimami]

Hey, I need help!!!!

I don't think I am on the right subject, but I hope someone can help me.
I am from România, my English is not well...I hope someone understand what I want. ..


I need someone to tell me how is calculated the monthly total payments ( is 2 different values).

The fact is :
1. The credit : 80000
2. Period 5 years, 60 months
3. The monthly rate is 5% , the annual rate is 5X12= 60 %
4. With the condition to pay the monthly paymant on the contractual date , the bank give one discount of 1% monthly to the monthly 5%. ;ut if the monthly payment is not made in time, the monthly rate stay 5%.
5. Only with this condition the monthly rate is 4% , the annual rate is 48%.
So, I have to calculate the interest at the monthly 5% , annual 60% ? I used the PPMT function for this, I use the PTM function for the monthly basic payment, but to calculate the monthly payment with discount haw I can do that?

Thank you!!!!

[Pete_UK]

@Deitimami

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Pete

[Pete_UK]

@NIKNIKNIK,

Try this formula:

=SUMIFS(Sheet2!B$2:B$33,Sheet2!C$2:C$33,"*"&B2&"*",Sheet1!K$2:K$33,">="&Sheet2!A2,Sheet1!K$2:K$33,"<"&Sheet1!A2+7)

I don't know which sheet the formula is on, nor do I know if you want to count a week ahead from A2 (assumed in the above formula) or a week behind. It would help if you posted a sample Excel workbook, then I could have seen for myself.

Hope this helps.

Pete

[NIKNIKNIK]

Haven't tried your new formula yet but here's an example workbook with the first formula working perfectly, I'm sure you'll be able to see what I'm trying to do from this as far as sku/date/etcCO Chart example.xlsx

[Pete_UK]

Your date is in L2. You will need to use this formula in O2:

=SUMIFS(Sheet2!B$2:B$33,Sheet2!C$2:C$33,"*"&B2&"*",Sheet2!A$2:A$33,">="&L2,Sheet2!A$2:A$33,"<"&L2+7)

Hope this helps.

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

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[Deitimami]

Thank you, Pete!
I'm sorry for the inconvenience.
I'm new here, I learn...

Have a nice day!

[NIKNIKNIK]

Ahh thank you, that's nearly perfect, is there a way I can do a greater than/less than for the date? in further columns after week one, I want to pull results from THAT week, for example week two would be results greater than 7 days but less than 15, how would I express that? I tried changing "<"&L2+7 to ">"&L2+7,"<"&L2+14 but I get a too few arguments alert.

[Pete_UK]

The first comparison is greater than or equal to the date in L2 (i.e. including that date) and the second comparison is less than the date plus 7 - that is still seven days, i.e. 12/03, 13/03, 14/03, 15/03, 16/03, 17/03 and 18/03. The next week will be greater than or equal to L2 +7 (i.e. from 19th March) and less than L2+14. For the third week it will be ">="&L2+14 and "<"&L2+21, so you can see that you are just adding 7 onto the previous term each time.

Hope this helps.

Pete

[NIKNIKNIK]

Alright i'm trying that right now for week two but =SUMIFS(Sheet2!B$2:B$33,Sheet2!C$2:C$33,"*"&B2&"*",Sheet2!A$2:A$33,">="&L2,Sheet2!A$2:A$33,">="&L2+7,"<"&L2+14) is giving me a too few arguments alert. What am I doing wrong there? I really appreciate all your help so far by the way.

[Pete_UK]

This is the formula I gave you in Post #12:

=SUMIFS(Sheet2!B$2:B$33,Sheet2!C$2:C$33,"*"&B2&"*",Sheet2!A$2:A$33,">="&L2,Sheet2!A$2:A$33,"<"&L2+7)

and this is what is should become for subsequent weeks (changes shown in red):

=SUMIFS(Sheet2!B$2:B$33,Sheet2!C$2:C$33,"*"&B2&"*",Sheet2!A$2:A$33,">="&L2+7,Sheet2!A$2:A$33,"<"&L2+14)

=SUMIFS(Sheet2!B$2:B$33,Sheet2!C$2:C$33,"*"&B2&"*",Sheet2!A$2:A$33,">="&L2+14,Sheet2!A$2:A$33,"<"&L2+21)

=SUMIFS(Sheet2!B$2:B$33,Sheet2!C$2:C$33,"*"&B2&"*",Sheet2!A$2:A$33,">="&L2+21,Sheet2!A$2:A$33,"<"&L2+28)

and so on.

Hope this helps.

Pete

[NIKNIKNIK]

Ahh that's it, perfect. Thanks for all your help, saved me a lot of trial and error.