vlookup and sum functions.

[benetty]

vlookup gives only the first matching value. How to get all matching values summed up in a single cell?

[Jonmo1]

Try the SUMIF function.

[benetty]

Thanks, but I am still having problem! here is a better explanation:

Acount 01 Acount 02 Acount 03
Actual ($250,00) ($100,00) ($800,00)
foreseen (350,00) ($500,00) (500,00)

Date: 11/2/2016
So, I want excel to summ up and display from a table, all the values of Actual and Foreseen
to any date I type on a cell linked to the Date Cell. is it clear? thanks

[Jonmo1]

Can you post the Vlookup formula which returns the first matching value ?

[benetty]

Sr. I didn't do any lookup. I did them manually. my point is, can you help me with a formula that will give me these sums...?

[JohnTopley]

Please post a sample file (not image) showing expected results.

To Attach a File:

1. Click "Go Advanced"
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[Jonmo1]

You started your thread with, Quote

vlookup gives only the first matching value

I take that to mean that your data is setup in such a way that vlookup will successfully return the value of the first match.

If I can see what that vlookup would look like, I can transate it to sumif instead so it will add all the matching values together.

[Jonmo1]

Sumif is basically like this

=SUMIF(A1:A100,"Actual",B1:B100)

This will sum all the values in B1:B100 corresponding to the cells that contain 'Actual' in A1:A100

[benetty]

Here is my excel file attached... still the same question on Vlookup, Left, and If.
Thank you so much!

[JohnTopley]

No file attached.

[benetty]

Sorry! do you see it now? I just edit the same previouse message and attached it.

[JeteMc]

OK, I feel rather confident that the formula for C4:C11 could be:

=SUMIFS(E$26:E$95,H$26:H$95,B4)

Similarly D4:D11 could be:

=SUMIFS(G$26:G$95,H$26:H$95,B4)

I am a bit less confident about the formulas for E21:F22 which I feel may be similar to:

=SUMIFS(E$26:E$95,D$26:D$95,"<="&$B$20)

Which by the way I am totally guessing could also be the formula that populates B21.
If I am off on any of these I would suggest that you manually insert the value that you expect to see in that cell so that we'll have a target.
Let us know if you have any questions.

[benetty]

Thank you so much for your help!

[benetty]

What should I learn to be able to work with a lot of datas in a single excel sheet like this example attached?

[JeteMc]

Not sure what it is that you want to do with the data in the file attached to post #14. My suggestion is that you start a new thread asking for specific help with the question(s) that you have about using the data.
Responding to post #13: Your Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.