LinkBack URL
About LinkBacks
Does anyone know of a good way to multiply of doing a SumProduct if? Something like multiply then sum all values in column A and B only when column C equals a certain flag. The values to multiply and then sum would not be in order?
Does anyone know of a good way to multiply of doing a SumProduct if? Something like multiply then sum all values in column A and B only when column C equals a certain flag. The values to multiply and then sum would not be in order?
You mean something like:
=Sumproduct(--(C1:C100="flag"),A1:A100,B1:B100)
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Perfect!! Thanks.
I know this is an old thread, but can someone tell me what the --( part of the =Sumproduct(--(C1:C100="flag"),A1:A100,B1:B100) formula means?
Thank you!
It coerces the TRUE/FALSE results for each conditional argument to 1/0 results, so Sumproduct can do the math.
Is there a way to multiply the two columns using two flags? In other words, multiply values Columns A and B only when Column C = Flag1 and Column D = Flag2
=SUMPRODUCT(--(C1:C100="flag1")*(D1:D100="Flag2"),A1:A100,B1:B100)
Palmetto
Do you know . . . ?
You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
Sorry to unbury this thread, but my doubt is concerning this command "sumproduct".
I'm using =sumproduct(--(A1:J1=A2:J2)) to count same values at the same column between the two lines, works great.
But I don't want to count empty cells. As example, when there is an empty cell in A3 and in B3, it counts as same.
Is there a way to input some (if <> "") in there?
Example:
A__DEFGHIJ
AB__CDGHIJ
(suppose _ is a empty cell). In those lines, i'm counting A, _, G, H, I, J. And I don't want to count the empty one.
Thanks in advance.
edit:
nevermind!!!
Just made it!, used the double flag stuff: =sumproduct(--(A1:J1=A2:J2)*(A1:J1<>""))
Last edited by Aderbalito; 03-03-2013 at 07:45 PM.
Aderbalito,
Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Excel's Sumif and Sumproduct with multiple criteria
https://www.youtube.com/watch?v=6l5wpZrVvT0
Combining Sumproduct and VLookup function in Excel
https://www.youtube.com/watch?v=lkT9OAgcBuw
Last edited by huyle2103; 08-24-2015 at 08:40 PM.
Tips Excel: http://goo.gl/EXAjwP
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks