SUMIF OR SUMPRODUCT

[mapakunk]

which is the right thing to use to add the following scenerios? (Sp)

A....I......I...G..I
DatesI......IvaluesI

Add anything in g:g where column a is due
between:

today and 15 days ago,
sumif(a:a,(between?)today() & today()15, G:G)?

16 & 30 days ago

31 & 60 days ago


you get the idea

I just can't seem to figure out how to tell excel the (Between) part


[ This Message was edited by: mapakunk on 2002-05-16 10:08 ]

[Aladin Akyurek]

On 2002-05-16 10:07, mapakunk wrote:
which is the right thing to use to add the following scenerios? (Sp)

A....I......I...G..I
DatesI......IvaluesI

Add anything in g:g where column a is due
between:

today and 15 days ago,
sumif(a:a,(between?)today() & today()15, G:G)?

16 & 30 days ago

31 & 60 days ago


you get the idea

I just can't seem to figure out how to tell excel the (Between) part


[ This Message was edited by: mapakunk on 2002-05-16 10:08 ]

You can use both when "between" numerical conditions/criteria involved.

=SUMIF(A:A,">="&TODAY()-15,G:G)

BTW, logically the foregoing is sufficient for from 15 days ago up to TODAY().

"16 & 30 days ago" would be:

=SUMIF(A:A,">="&TODAY()-30,G:G)-SUMIF(A:A,">"&TODAY()-16,G:G)

The SUMPRODUCT version of the latter would look like:

=SUMPRODUCT((A2:A100>=TODAY()-30)*(A2:A100<=TODAY()-16),G2:G100)

SUMIF is cheaper, should therefore be prefered above SUMPRODUCT whenever possible.

Another alternative is of course DSUM (whose set up is not easy when the required formula must be copied down/across for similar conditions).

Aladin


[ This Message was edited by: Aladin Akyurek on 2002-05-16 10:35 ]

[mapakunk]

Aladin,

Thank you for your assistance so far. You have helped me a great deal. So what I am seeing is that the reason I was having trouble was because my '<' signs were not in " ". Why do they need to be in quotes. I thought those were for text strings...

[Aladin Akyurek]

On 2002-05-16 10:39, mapakunk wrote:
Aladin,

Thank you for your assistance so far. You have helped me a great deal. So what I am seeing is that the reason I was having trouble was because my '<' signs were not in " ". Why do they need to be in quotes. I thought those were for text strings...

That's just a syntactic requirement for SUMIF as well as for COUNTIF. It enables the parser's error-handling component (I think) to distinguish between SUMIF/COUNTIF expressions and expressions which (also) use relational operators (>,<,etc) as in B1>D1.

[mapakunk]

Wow. There sure is alot going on in the background of this crazy program isn't there.

Thanks again

kay TTFN

mapakunk