Do I need SUMPRODUCT?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DaveMoore - 10 Nov 2006 09:43 GMT

The following is a formula I am using that totals a range meeting a
single criteria.

=SUMIF(D2:D254,D507,E2:E254)

However, I now want to give an additional condition; that is
(F2:F254) = ""

I think I want SUMPRODUCT but I am not sure how to use it?
Can anyone help?
My thanks to those who can.

Dave Moore

Reply to this Message

DaveMoore - 10 Nov 2006 09:59 GMT

Got it!

=SUMPRODUCT(($D$2:$D502=D507)*($F$2:$F502="")*($E$2:$E502))

works for me.

Are there any shorter versions?

Reply to this Message

Arvi Laanemets - 10 Nov 2006 11:50 GMT

Hi

Not shorter, but this one may give you some gain in speed, when you have a
lot of such formulas

=SUMPRODUCT(--($D$2:$D502=D507),--($F$2:$F502=""),($E$2:$E502))

Signature

Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

> Got it!
>
[quoted text clipped - 3 lines]
>
> Are there any shorter versions?

Reply to this Message

Teethless mama - 10 Nov 2006 20:49 GMT

SUM(($D$2:$D502=D507)*($F$2:$F502="")*($E$2:$E502))

Fewer key stroke than sumproduct
ctrl>shift enter (not just enter)

> Got it!
>
[quoted text clipped - 3 lines]
>
> Are there any shorter versions?

Reply to this Message