| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF Range Help
I am using the SUMIF function to figure out the instances of a risk factor.
the range of the risk factors span across three columns (K9:M36). the criteria is from A to E and it's looking at another column to add the value based on the risk factor. the formula now is only looking at values in column K and M and 'ignoring' the middle column. is there a fix for this? any help would be greatly appreciated. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF Range Help
are you using it?
=sumif(a9:e36,"criteria",k9:m36) -- regards from Brazil Thanks in advance for your feedback. Marcelo "LSalazar" escreveu: I am using the SUMIF function to figure out the instances of a risk factor. the range of the risk factors span across three columns (K9:M36). the criteria is from A to E and it's looking at another column to add the value based on the risk factor. the formula now is only looking at values in column K and M and 'ignoring' the middle column. is there a fix for this? any help would be greatly appreciated. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF Range Help
No. this is the formula =SUMIF(K9:M36,J40,F9:F36) there are only three
columns for the risk criteria so range k9:m36 "Marcelo" wrote: are you using it? =sumif(a9:e36,"criteria",k9:m36) -- regards from Brazil Thanks in advance for your feedback. Marcelo "LSalazar" escreveu: I am using the SUMIF function to figure out the instances of a risk factor. the range of the risk factors span across three columns (K9:M36). the criteria is from A to E and it's looking at another column to add the value based on the risk factor. the formula now is only looking at values in column K and M and 'ignoring' the middle column. is there a fix for this? any help would be greatly appreciated. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF Range Help
If the same criteria will not exist in multiple rows of your three columns
(K, L, M), then you might try this. =SUMPRODUCT(--(K9:K36=J40) + (L9:L36=J40) +(M9:M36=J40),F9:F36) To explain better, assuming that "A" is the criteria you want to find...if "A" appears in K9 AND it appears in M9 (which is the same row), then the formula I provided will SUM the corresponding row of column F twice, and assume that wouldn't work. However, if the specific criteria to match (from J40) can only exist in one of the three columns (per row) you are looking in, then the formula should do what you want. HTH, Paul -- "LSalazar" wrote in message ... No. this is the formula =SUMIF(K9:M36,J40,F9:F36) there are only three columns for the risk criteria so range k9:m36 "Marcelo" wrote: are you using it? =sumif(a9:e36,"criteria",k9:m36) -- regards from Brazil Thanks in advance for your feedback. Marcelo "LSalazar" escreveu: I am using the SUMIF function to figure out the instances of a risk factor. the range of the risk factors span across three columns (K9:M36). the criteria is from A to E and it's looking at another column to add the value based on the risk factor. the formula now is only looking at values in column K and M and 'ignoring' the middle column. is there a fix for this? any help would be greatly appreciated. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF Range Help
Fantastic! that did it. awesome. thanx!!!
"PCLIVE" wrote: If the same criteria will not exist in multiple rows of your three columns (K, L, M), then you might try this. =SUMPRODUCT(--(K9:K36=J40) + (L9:L36=J40) +(M9:M36=J40),F9:F36) To explain better, assuming that "A" is the criteria you want to find...if "A" appears in K9 AND it appears in M9 (which is the same row), then the formula I provided will SUM the corresponding row of column F twice, and assume that wouldn't work. However, if the specific criteria to match (from J40) can only exist in one of the three columns (per row) you are looking in, then the formula should do what you want. HTH, Paul -- "LSalazar" wrote in message ... No. this is the formula =SUMIF(K9:M36,J40,F9:F36) there are only three columns for the risk criteria so range k9:m36 "Marcelo" wrote: are you using it? =sumif(a9:e36,"criteria",k9:m36) -- regards from Brazil Thanks in advance for your feedback. Marcelo "LSalazar" escreveu: I am using the SUMIF function to figure out the instances of a risk factor. the range of the risk factors span across three columns (K9:M36). the criteria is from A to E and it's looking at another column to add the value based on the risk factor. the formula now is only looking at values in column K and M and 'ignoring' the middle column. is there a fix for this? any help would be greatly appreciated. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF Range Help
Shorter version:
=SUMPRODUCT((K9:M36=J40)*F9:F36) "PCLIVE" wrote: If the same criteria will not exist in multiple rows of your three columns (K, L, M), then you might try this. =SUMPRODUCT(--(K9:K36=J40) + (L9:L36=J40) +(M9:M36=J40),F9:F36) To explain better, assuming that "A" is the criteria you want to find...if "A" appears in K9 AND it appears in M9 (which is the same row), then the formula I provided will SUM the corresponding row of column F twice, and assume that wouldn't work. However, if the specific criteria to match (from J40) can only exist in one of the three columns (per row) you are looking in, then the formula should do what you want. HTH, Paul -- "LSalazar" wrote in message ... No. this is the formula =SUMIF(K9:M36,J40,F9:F36) there are only three columns for the risk criteria so range k9:m36 "Marcelo" wrote: are you using it? =sumif(a9:e36,"criteria",k9:m36) -- regards from Brazil Thanks in advance for your feedback. Marcelo "LSalazar" escreveu: I am using the SUMIF function to figure out the instances of a risk factor. the range of the risk factors span across three columns (K9:M36). the criteria is from A to E and it's looking at another column to add the value based on the risk factor. the formula now is only looking at values in column K and M and 'ignoring' the middle column. is there a fix for this? any help would be greatly appreciated. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumif within a range | Excel Discussion (Misc queries) | |||
| Sumif range? | Excel Worksheet Functions | |||
| sumif() using range name | Excel Worksheet Functions | |||
| How to use a range in SUMIF? | Excel Worksheet Functions | |||
| SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
Linear Mode
