Convert SUMIFS formula from 2007 to 2003

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alex.W - 15 Apr 2008 03:18 GMT

I have this formula that works in Excel 2007. I need to get it to work in
Excel 2003. Can anyone help?

SUM(COUNTIFS('Sheet1'!B6:B100,"incident",'Sheet1'!T6:T100,B4)+COUNTIFS('Sheet2'!B6:B100,"incident",'Sheet2'!T6:T100,B4))

Alex.W

Reply to this Message

T. Valko - 15 Apr 2008 03:25 GMT

Try this:

=SUMPRODUCT(--(Sheet1!B6:B100="incident"),--(Sheet1!T6:T100=B4))+SUMPRODUCT(--(Sheet2!B6:B100="incident"),--(Sheet2!T6:T100=B4))

Signature

Biff
Microsoft Excel MVP

>I have this formula that works in Excel 2007. I need to get it to work in
> Excel 2003. Can anyone help?
>
> SUM(COUNTIFS('Sheet1'!B6:B100,"incident",'Sheet1'!T6:T100,B4)+COUNTIFS('Sheet2'!B6:B100,"incident",'Sheet2'!T6:T100,B4))
>
> Alex.W

Reply to this Message

Alex.W - 16 Apr 2008 03:16 GMT

Many thanks it works well.

It did however highlight another problem you might be able to assist with.
Using the same formula (Sheet1!B6:B100) how do I get it to return entries
that are in a range? For example >10 but <=20.

Alex.W

> Try this:
>
[quoted text clipped - 6 lines]
> >
> > Alex.W

Reply to this Message

T. Valko - 16 Apr 2008 04:46 GMT

>> =SUMPRODUCT(--(Sheet1!B6:B100="incident"),--(Sheet1!T6:T100=B4))+SUMPRODUCT(--(Sheet2!B6:B100="incident"),--(Sheet2!T6:T100=B4))

> Using the same formula (Sheet1!B6:B100) how do I get it to return entries
> that are in a range? For example >10 but <=20.

Well, "using the same formula" won't work. You're already testing B6:B100 to
see if it equals "incident". That range can't = incident *and* be >10 but
<=20 at the same time!

Add 2 arrays:

--(Sheet1!B6:B100>10),--(Sheet1!B6:B100<=20)

Signature

Biff
Microsoft Excel MVP

> Many thanks it works well.
>
[quoted text clipped - 15 lines]
>> >
>> > Alex.W

Reply to this Message

attila nemet - 22 May 2008 17:22 GMT

Hi,

I would like to use the next formula in excel2003.

Please tell me if you know any solution.

=SUMIFS(E$5:E$33;D$5:D$33;"TXNS1***")

Thank you

Attila

Reply to this Message

MyVeryOwnSelf - 22 May 2008 18:27 GMT

> I would like to use the next formula in excel2003.
> =SUMIFS(E$5:E$33;D$5:D$33;"TXNS1***")

Maybe what you're looking for is
=SUMIF(E$5:E$33,"TXNS1***",D$5:D$33)

Reply to this Message

Bernard Liengme - 22 May 2008 22:07 GMT

In SUMIFS (Excel 2007 only) the sytax is
=SUMIFS(range_to_sum, )
and the last two arguments can be repeated many times

The syntax for SUMIF is
=SUMIF(range_to_test, criteria, range_to_sum)

best wishes

Signature

Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

> Hi,
>
[quoted text clipped - 7 lines]
>
> Attila

Reply to this Message

Funso - 02 Sep 2008 17:36 GMT

Please can you help me with this formula too. The SUMIFS section doesn't seem
to be working.

=IF($D$1="All",SUMIFS('Master Sheet'!$W$20:$W$1028,'Master
Sheet'!$L$20:$L$1028,$D$2,'Master Sheet'!$D$20:$D$1028,$A4,'Master
Sheet'!$O$20:$O$1028,B$3,'Master Sheet'!$W$20:$W$1028,$G$2),SUMIFS('Master
Sheet'!$W$20:$W$1028,'Master Sheet'!$J$20:$J$1028,$D$1,'Master
Sheet'!$L$20:$L$1028,$D$2,'Master Sheet'!$D$20:$D$1028,$A4,'Master
Sheet'!$O$20:$O$1028,B$3,'Master Sheet'!$W$20:$W$1028,$G$2))

I would appreciate it if anyone could help me convert it to an Excel 2003
formula. This is very urgent.

Thanks.

Reply to this Message

Bob Phillips - 02 Sep 2008 18:10 GMT

=IF($D$1="All",SUMPRODUCT(
--('Master Sheet'!$L$20:$L$1028=$D$2),--('Master Sheet'!$D$20:$D$1028=$A4),
--('Master Sheet'!$O$20:$O$1028=B$3),--('Master
Sheet'!$W$20:$W$1028=$G$2),'Master Sheet'!$W$20:$W$1028),
SUMPRODUCT(
--('Master Sheet'!$J$20:$J$1028=$D$1),--('Master Sheet'!$L$20:$L$1028=$D$2),
--('Master Sheet'!$D$20:$D$1028=$A4),--('Master Sheet'!$O$20:$O$1028=B$3),
--('Master Sheet'!$W$20:$W$1028=$G$2),'Master Sheet'!$W$20:$W$1028))

Signature

__________________________________
HTH

Bob

> Please can you help me with this formula too. The SUMIFS section doesn't
> seem
[quoted text clipped - 11 lines]
>
> Thanks.

Reply to this Message