# Thread: Help with what formula to use

1. ## Help with what formula to use

Register for a FREE account, and/
or Log in to avoid these ads!

I am trying to match dates on 2 different sheets, then do a calculation where I take the amount under date on sheet 1 and divide by amount under matching date on sheet 2.
What is the best way to do this?

2. Good afternoon,

It's hard to tell without data, but SUMIFS is usually a winner.

Hope this helps,

3. Originally Posted by bgoree09
Good afternoon,

It's hard to tell without data, but SUMIFS is usually a winner.

Hope this helps,

Not really. I have attached a mini version of my spreadsheet. Keep in mind that sheet 1 is a pivot table.
What I need to do is a calculation from the information under the date on sheet one with the information under the same date on page 2.

For example, on date of 20140808, I need to take 3425 and divide it by 72 on sheet 2.
I would like the information printed under the line on sheet 1.
Hope this makes sense. Thanks!

4. Hello again,

Still a little fuzzy on the details, but here goes. Please see the attached file. Let me know if I'm off.

Hope this helps,

5. Originally Posted by bgoree09
Hello again,

Still a little fuzzy on the details, but here goes. Please see the attached file. Let me know if I'm off.

Hope this helps,

That appears to be correct, but I am not sure exactly how you got it. I see the formula, but I don't understand what you did to get it. Could you please explain to me? I really appreciate the help!

6. Certainly. You are very welcome.

Index-match breaks down something like this:

INDEX ( Answer Range , MATCH ( Single Search Criteria , Criteria Range , 0 - exact match -))

So, in english, this will take the search date and find it in the row of dates, then return the corresponding reference number. The only hiccup was that on Sheet1 the dates were text fields and on Sheet2 they were a numbers. I added zero to the search date within the formula to correct the format.

Hope this helps,

7. It may be some time until I can work with this, but will save it. Thank you so much!

8. Surely, this is one for HLOOKUP?

=A4/HLOOKUP(--A3,Sheet2!A3:C4,2)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•