Modifying sumif formulas.
 

=SUMIF(B:B,B92,AE:AE)+SUMIF(B:B,B92,AI:AI)+sumif(b :b,b92,AK:AK)

This was the start to one formula on a spread sheet, my simple question is
can this be condensed. is there such thing as
=sumif(b:b,b92,ae:ae&ai:ai&ak:ak) -

The whole purpose of condensing the formula is to be allow others to follow
the path especially auditors.

Is this easier to understand?

=SUMPRODUCT((B1:B1000=B92)*(AK1:AK1000+AE1:AE1000+ AI1:AI1000))

With Sumproduct however, you can't use total column references (B:B, AK:AK,
.... etc.).
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Hirsch" wrote in message
...
=SUMIF(B:B,B92,AE:AE)+SUMIF(B:B,B92,AI:AI)+sumif(b :b,b92,AK:AK)

This was the start to one formula on a spread sheet, my simple question is
can this be condensed. is there such thing as
=sumif(b:b,b92,ae:ae&ai:ai&ak:ak) -

The whole purpose of condensing the formula is to be allow others to
follow
the path especially auditors.

no the existing formula is about as simple as you will be able to get

"Hirsch" wrote:

=SUMIF(B:B,B92,AE:AE)+SUMIF(B:B,B92,AI:AI)+sumif(b :b,b92,AK:AK)

This was the start to one formula on a spread sheet, my simple question is
can this be condensed. is there such thing as
=sumif(b:b,b92,ae:ae&ai:ai&ak:ak) -

The whole purpose of condensing the formula is to be allow others to follow
the path especially auditors.

Hirsch, you can use the formula DSUM. This will sum based on criteria you set
outside the data by using the Column heading as the reference and then your
criteria underneath. The help menu has a useful entry on this formula.

HTH,

Kevin M
MOS Excel Specialist

"Hirsch" wrote:

=SUMIF(B:B,B92,AE:AE)+SUMIF(B:B,B92,AI:AI)+sumif(b :b,b92,AK:AK)

This was the start to one formula on a spread sheet, my simple question is
can this be condensed. is there such thing as
=sumif(b:b,b92,ae:ae&ai:ai&ak:ak) -

The whole purpose of condensing the formula is to be allow others to follow
the path especially auditors.

"Kevin M" wrote...
Hirsch, you can use the formula DSUM. This will sum based on
criteria you set outside the data by using the Column heading
as the reference and then your criteria underneath. The help
menu has a useful entry on this formula.
....
"Hirsch" wrote:
=SUMIF(B:B,B92,AE:AE)+SUMIF(B:B,B92,AI:AI)+sumif (b:b,b92,AK:AK)
....


How would DSUM help in this case? Neither SUMIF nor DSUM can directly sum
multiple columns/fields. It's hard to see how something like

=DSUM($A:$AZ,31,CR)+DSUM($A:$AZ,35,CR)+DSUM($A:$AZ ,37,CR)

would be all that much of an improvement. Note that CR is a reference to a
criteria range.

This can be condensed with trickery,

=SUM(SUMIF(B:B,B92,OFFSET(AE:AE,0,{0,4,6}))

but the result is obscure at best.