Multiple DSUM criteria (MONTH & a numeric value) between worksheet

[sarah]

Hello. I'm trying to use DSUM to calculate a row of quantities that fit the
criteria of a specific month and size. Here's an image explaining what I am
trying to achieve:

http://i37.tinypic.com/ergxsx.jpg

I have a DSUM formula included in the picture, which is not working.
Any help would be greatly appreciated.

Thank you,

sarah

 

[Peo Sjoblom]

First of all don't use the whole sheet as your table, use the real table

secondly when you need to use a formula in the criteria put no header in the
criteria

here's a simplification, assume you table including headers
is A4:C10

assume the criteria is E1:E2 with E1 blank and E2 has this formula

=AND(MONTH(A5)=4,B5=6)

where A5 is the first cell in the date column with a date (header in A4)
and B5 the first with a size, so that formula should return FALSE using your
example

the formula would be

=DSUM(A4:C10,"Amount",E1:E2)

returns 550

Of course the D functions are pretty old fashioned and you could easily use
SUMIFS or
SUMPRODUCT

=SUMPRODUCT(--(MONTH(A5:A10)=4),--(B5:B10=6),C5:C10)



--


Regards,


Peo Sjoblom

 

[Tom Hutchins]

DSUM can't find MONTH)4), your Date criteria , in any of the actual dates in
the data. They must be the same kind of data. I got it to work this way:

Remove the apostrophe from your DSUM formula, if there is one, so it is a
live formula again.

Insert a new column B on Sheet1. Giveit a heading of Month in B1 and this
formula in B2:
=Month(A2)
Format B2 as a Number with no decimal places, then copy it down through all
your rows of data.

On Sheet2, change Date in your Criteria to Month in A9, and put the desired
month number (4) in A10.

Your formula should work now.

You could also use a SUMPRODUCT formula like the following on Sheet2 to get
the same result using your original data (don't have to add the Month column):

=SUMPRODUCT(--(MONTH(Sheet1!A2:A10000)=$A$10),--(Sheet1!B2:B10000=$B$10),Sheet1!C2:C10000)

Hope this helps,

Hutch

 

[sarah]

What if a new row / new data is introduced to the table? How can I ensure
that it will be picked up by my formula (without having to change the
formula), if the whole sheet is not selected?

Thank you for your reply!

 

[Peo Sjoblom]

But would you add over a million? And how many columns would you use. If the
table is that big it is better to store it in Access and use a query in
Excel.

Regardless how big the table is you would need to use the method with a
formula criteria

I would probably use maybe A1:C10000 to start with, not one that is
1048576x16384 which is a huge size.


--


Regards,


Peo Sjoblom