Is it possible to use the SUMIF formula with 2 criterias? For example I have column with "Apple", "Pear" and in other column "Red", "Green".
Now I want to use the SUMIF formula for all the red apples (not counting the green apples)or all the green pears, etc.
Last edited by pansovic; 09-08-2010 at 01:05 PM.
In Excel 2007 you can use SUMIFS (with an "S" at the end), e.g.
=SUMIFS(C:C,A:A,"Apple",B:B,"Red")
to sum column C if you have fruit in column A and colour in B
Audere est facere
Thanks but I am getting a Value error.
I send you attached my file. I am trying to create the formula in Sheet1 C2. I have to add up all the numbers in column P and Q of the sheet Inventario with the criteria in column G and H, Sheet Inventario. Take note that the values in those columns are sometimes a text/number or as well result of a formula.
You can't sum a two column range with SUMIFS if the criteria ranges are single columns (there needs to be a 1 to 1 relationship). Try using an "array formula" like this
=SUM(IF((INVENTARIO!G$3:G$1000="CH")*(INVENTARIO!H$3:H$1000="EL"),INVENTARIO!P3:Q1000))
Formula needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar
For INVENTARIO!G$3:G$1000 and INVENTARIO!H$3:H$1000 I receive a Value Error.
Did this formula worked for you? What am I doing wrong?
The above formulas don't work one way or another.
The only way I can do it is by creating a new column and create a formula to take "Apple" from column A and "Red" from column B so that I have in column C "Apple Red", "Pear Green", "Apple Green", etc. Than from that cell I make a simple SUMIFS formula. I than hide the new created column as that it has no use except for making the formula work.
So by this I consider this case closed
Last edited by pansovic; 09-07-2010 at 06:11 PM.
Hi,
One of your problems is that the apparent "CH" values in the Inventario sheet have four preceding spaces, i.e. ' CH'. (there should be 4 spaces before CH). Either correct that by modifying the formula which adds the spaces, or wrap the whole thing in a TRIM() function, then use the following.
Alternatively use " CH" in the formula above. (there should be 4 spaces before CH).
Please Login or Register to view this content.
Regards
Last edited by Richard Buttrey; 09-07-2010 at 06:14 PM.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
I will have to study the TRIM function but is it possible that the Spanish SUMAR.SI is different from the SUMIFS? With SUMAR.SI I have to fill in (RANGE,CRITERIA,RANGE SUM) while in your example you put the RANG SUM in the beginning.
Hi,
Are you using Excel 2007 or 2003. The SUMIFS() are new to 2007 and if you're using 2003 then the SUMIF() - singular only takes one criteria. It sounds like you may only have the SUMIF version.
I suggest therefore that you add a helper column to your data and concatenate the G&H column cells. i.e. you'll get ' CHEL'. Now use this helper column in the SUMIF, i.e.
SUMIF(helpercolumn," CHEL",sum range)
HTH
This is indeed what I have done but I DO have Excel 2007. Maybe the Spanish version is not that advanced as the English version?
Inventario resolved.xlsmI attached the file with the correct formulmOriginally Posted by pansovic
=SUMAR.SI.CONJUNTO(INVENTARIO!$P:$P,INVENTARIO!$G:$G,$C3,INVENTARIO!$H:$H,$D3)
You need to separate the creterial in ordert to look the information
Columm B Columm C Columm D
CH EL CH EL
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks