SUMIF with the Sum_range across several colums
 

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?

SUMIF with the Sum_range across several colums
 

Hi,

With an array formula ..

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

Use Control Shift Enter to enter formula.

HTH
Cheers
Carim

SUMIF with the Sum_range across several colums
 

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

"Carim" wrote:

Hi,

With an array formula ..

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

Use Control Shift Enter to enter formula.

HTH
Cheers
Carim

SUMIF with the Sum_range across several colums
 

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" wrote in message
...
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

"Carim" wrote:

Hi,

With an array formula ..

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

Use Control Shift Enter to enter formula.

HTH
Cheers
Carim

SUMIF with the Sum_range across several colums
 

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

"Biff" wrote:

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" wrote in message
...
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

"Carim" wrote:

Hi,

With an array formula ..

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

Use Control Shift Enter to enter formula.

HTH
Cheers
Carim

SUMIF with the Sum_range across several colums
 

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

"Biff" wrote in message ...
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" wrote in message
...
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

"Carim" wrote:

Hi,

With an array formula ..

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

Use Control Shift Enter to enter formula.

HTH
Cheers
Carim

SUMIF with the Sum_range across several colums
 

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

"Epinn" wrote in message
...
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

"Biff" wrote in message
...
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" wrote in message
...
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

"Carim" wrote:

Hi,

With an array formula ..

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

Use Control Shift Enter to enter formula.

HTH
Cheers
Carim

SUMIF with the Sum_range across several colums
 

You're welcome. Thanks for the feedback!

Biff

"D" wrote in message
...
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

"Biff" wrote:

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" wrote in message
...
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

"Carim" wrote:

Hi,

With an array formula ..

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

Use Control Shift Enter to enter formula.

HTH
Cheers
Carim