SUMIF with the Sum_range across several colums

[D]

I would like to use SUMIF to add up numbers occurring in a row that spans 12
columns (months of the year) if the condition is met in the same row in
another column.

This is how I tried it: =SUMIF(B2:B13, "=STA", H2:S13)

The conditon STA can appear in several rows in the range in column B.

It works perfectly if there is only one column in the sum_range, but not
with more than one.

Can this be done?

 

[Carim]

Hi,

With an array formula ..

{=SUM(IF((B2:B13)="STA", H2:S13))}

Use Control Shift Enter to enter formula.

HTH
Cheers
Carim

 

[D]

HI, Carim - thank you SO much for the reply! I have typed in the formula and
held down ctrl+shift and pressed enter and all I get is the formula showing
as text in the cell?????

D

 

[Biff]

Select the formula cell.

Format the cell as GENERAL

Hit function key F2 then ENTER.

Use this formula (normally entered):

=SUMPRODUCT((B2:B13="sta")*(H2:S13))

Biff

 

[D]

You are a genius! Thank you so much for your generosity in sharing this
knowledge - I will now go and find out more about the SUMPRODUCT function.

I am very grateful
D

 

[Epinn]

Biff,

When I first read the post, I wanted to suggest SUM(IF( )) array formula and SUMPRODUCT which I prefer. But I was very much discouraged by my other experience, so I decided to keep quiet. Remember one user kept asking about COUNTA? I attempted to answer without realizing that I was only reading *part 2* of a two-part thread. I ended up totally off track. Would I have been okay if he truly wanted to count? (I need some encouragement. <g>) You, on the other hand, were so smart (and experienced) that you knew he wanted to create a dynamic range. I guess this is what D meant by genius.

D,

Welcome to the SUMPRODUCT club. I am a fan. I try to use SUMPRODUCT whenever possible. I don't like SUM(IF()) any more. SUMIF is the least dynamic. Biff, please correct me if I am wrong. These are two links that have helped me learn about SUMPRODUCT:-

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
and
http://mcgimpsey.com/excel/formulae/doubleneg.html

Bob,

How do you like the idea of SUMPRODUCT fan club? <bg> You know I have to make learning fun.

Epinn

Select the formula cell.

Format the cell as GENERAL

Hit function key F2 then ENTER.

Use this formula (normally entered):

=SUMPRODUCT((B2:B13="sta")*(H2:S13))

Biff

 

[Biff]

SUMIF is the least dynamic.

I would describe it differently. SUMIF isn't as versatile as SUMPRODUCT but
where they are interchangeable you should use SUMIF first. It's faster. The
same applies to COUNTIF.

Biff

Biff,

When I first read the post, I wanted to suggest SUM(IF( )) array formula and
SUMPRODUCT which I prefer. But I was very much discouraged by my other
experience, so I decided to keep quiet. Remember one user kept asking about
COUNTA? I attempted to answer without realizing that I was only reading
*part 2* of a two-part thread. I ended up totally off track. Would I have
been okay if he truly wanted to count? (I need some encouragement. <g>)
You, on the other hand, were so smart (and experienced) that you knew he
wanted to create a dynamic range. I guess this is what D meant by genius.

D,

Welcome to the SUMPRODUCT club. I am a fan. I try to use SUMPRODUCT
whenever possible. I don't like SUM(IF()) any more. SUMIF is the least
dynamic. Biff, please correct me if I am wrong. These are two links that
have helped me learn about SUMPRODUCT:-

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
and
http://mcgimpsey.com/excel/formulae/doubleneg.html

Bob,

How do you like the idea of SUMPRODUCT fan club? <bg> You know I have to
make learning fun.

Epinn

Select the formula cell.

Format the cell as GENERAL

Hit function key F2 then ENTER.

Use this formula (normally entered):

=SUMPRODUCT((B2:B13="sta")*(H2:S13))

Biff

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

You are a genius! Thank you so much for your generosity in sharing this
knowledge - I will now go and find out more about the SUMPRODUCT function.

I am very grateful
D