LinkBack URL
About LinkBacks
I want to sum up the values of Column A if its value is greater than the value in Column B in the same row.
A B
- -
6
2 1
3 6
2
4 6
1 1
In this case, the sum should be 6 + 2 = 8
Any advice on how to derive the formula?
Many thanks!
I want to sum up the values of Column A if its value is greater than the value in Column B in the same row.
A B
- -
6
2 1
3 6
2
4 6
1 1
In this case, the sum should be 6 + 2 = 8
Any advice on how to derive the formula?
Many thanks!
Try like this
=SUMPRODUCT((A2:A10>B2:B10)+0,A2:A10)
Last edited by daddylonglegs; 11-24-2017 at 06:21 PM.
Audere est facere
Thank you so much.
How about if need to add more one condition?
Sum up if Column Column C (Date) contains "17" only?
A B C
- - -
6 05-17
2 1 04-17
3 6 03-16
2 02-14
4 6 01-15
1 1 05-12
I tried this one. But it does not work. =SUMPRODUCT((A2:A10>B2:B10)*(C2:C10="*17*"),A2:A10)
Any advice?
maybe this one:
=SUMPRODUCT((A2:A7>B2:B7)*(RIGHT(C2:C7,2)="17"),A2:A7)
or
=SUMPRODUCT((A2:A7>B2:B7)*(--RIGHT(C2:C7,2)=17),A2:A7)
If column C contains true dates then try using YEAR function like this:
=SUMPRODUCT((A2:A10>B2:B10)*(YEAR(C2:C10)=2017),A2:A10)
or for date/not date
=SUM((A2:A7>B2:B7)*IF(ISNUMBER(C2:C7),YEAR(C2:C7)=2017,--RIGHT(C2:C7,2)=17)*A2:A7) with CSE
but
=SUMPRODUCT((A2:A10>B2:B10)*(YEAR(C2:C10)=2017)*A2:A10) works with text and numbers
Last edited by sandy666; 11-24-2017 at 09:50 PM.
Thank you for the advice. Column C contains true dates.
=SUMPRODUCT((A2:A10>B2:B10)*(YEAR(C2:C10)=2017),A2:A10)
This one works well in a test-spreadsheet (Left-hand side). But I can't understand why it does not work another spreadsheet (Right-hand side)
The top screenshots show the formula used. And the bottom ones show the outcomes.
Screenshot - 2.png.
Any advice what went wrong?
Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.
1. Make sure that your sample data are 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 results are also shown (mock up the results manually).
3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).
4. Try to avoid using merged cells as 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.
Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
#VALUE! -Incorrent Data Types. Check is there any value is in text? You check yourself. Press Alt-T-U-F evaluate the formula and slowly press enter and see which part of formula gives you error.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Sorry for uploading the images earlier, and know what to do next time.
And thanks for everyone's advice. I checked the Data Types. Some of the fields contain "-". So I deleted them and solved the issue.
If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
If you did it already - ignore it.
Thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks