Hello,
I'm looking to obtain a condition in a sumifs formula that will only take certain text criteria in one sumifs if possible. In this case, I want the sales for only certain countries, Peru, Bolivia and Brasil.
Attached, find the example. Please use the TestSumifsinctextsRevised file. Disregard the other one.
Thank you very much for your help.
Last edited by aurisab; 12-16-2012 at 07:19 PM.
It's quite hard to work out what you're trying to do from your example.
Is the attached the sort of thing you're after?
try the following...
=SUMIF(Sheet2!$B$2:$B$10,Sheet1!A2,Sheet2!$C$2:$C$10)
note, because C2 gives an error, I used A2 instead.
a question though - why are you trying to extract just the 1st 3 letters of the country?
edit: mis-read the question, try this...
=SUM(SUMIF(Sheet2!$B$2:$B$10,"Bolivia",Sheet2!$C$2:$C$10),SUMIF(Sheet2!$B$2:$B$10,"Peru",Sheet2!$C$2:$C$10),SUMIF(Sheet2!$B$2:$B$10,"Brazil",Sheet2!$C$2:$C$10))
Last edited by FDibbins; 12-16-2012 at 07:09 PM.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
You can use this array formula:
=SUM(IF(Sheet2!$B$2:$B$10={"BOLIVIA","BRASIL","PERU"},Sheet2!$C$2:$C$10))
Remember to apply with Ctrl + Shift + Enter, instead of just Enter!
- Moo
Hi FDibbins,
I have cleaned up the file. Sorry for the confusion.
Thank you for your help. Anyway that I the formula could be shorter?. Using a combination of Boliva, Peru and Brasil in one sumifs, instead of specifying for each.
I would not want to try the sumproduct formula, although it works but I dont understand it too well.
Hi Moo,
Thanks for your quick reply. Although, this works, I am using dynamic reports and it will be better that I can use enter instead of Ctrl + Shift + Enter. Any other possible way?
You can't do what you're asking for in a single SUMIFS, because SUMIFS has a logical 'And' between the conditions (i.e. it only adds the numbers if all of the conditions are met), whereas you need a logical 'Or' (it adds the numbers if any of the conditions are met).
So your options are to use an array formula, use a SUMPRODUCT or add together multiple SUMIF formula.
Originally Posted by Andrew-R
Hi Andrew-R,
Thanks for your prompt reply and explanation. I understand this formula now. This makes sense. I this case I will probably use the sumifs formula until I can better understand the sumproduct formula.
If you don't want an array formula, or multiple SUMIF solutions, then SUMPRODUCT will be the way to go. Let me explain:
Here is the formula you need in SUMPRODUCT format:
=SUMPRODUCT(($B$2:$B$10={"BOLIVIA","BRASIL","PERU"})*($C$2:$C$10))
What that is doing is it is creating matches... for each cell in B2:B10 that matches one of the three countries listed, it assigns that row a 1. For the non-matches, they are 0.
Then it multiplies all the rows with 1's by the values in column C, and sums that total. Very simple to understand once you see it in action and try it out.
- Moo
You can also use SUMIF like this
=SUM(SUMIF($B$2:$B$10,{"BOLIVIA","BRASIL","PERU"},$C$2:$C$10))
Audere est facere
cant you use sumifs? well i see you can just use sumif now!
=SUM(SUMIFS($C$2:$C$10,$B$2:$B$10,{"BOLIVIA","BRASIL","PERU"}))
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Hi Moo,
Your explanation helps. I get the Sumproduct formula now.
I will give it a try.
Thank you all.
Also see daddylonglegs and Martin's suggestions. Good work all around.
- Moo
Hi Daddylonglegs,
This works great.
Hi Martindwilson,
This is perfect.
Thanks!
This is a great community for help - lots of smart people who are glad to assist.
Be sure to leave feedback for any contributors who assisted you by clicking on the star located in the lower-left corner of one of their posts. It is always appreciated!
- Moo
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks