SUMIF Range Help

[LSalazar]

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.

 

[Marcelo]

are you using it?

=sumif(a9:e36,"criteria",k9:m36)


--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"LSalazar" escreveu:

 

[LSalazar]

No. this is the formula =SUMIF(K9:M36,J40,F9:F36) there are only three
columns for the risk criteria so range k9:m36

 

[PCLIVE]

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]

Fantastic! that did it. awesome. thanx!!!

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

 

[Teethless mama]

Shorter version:

=SUMPRODUCT((K9:M36=J40)*F9:F36)