Sum if multiple conditions fit

[Gabor]

Hi Everyone,

I would like to have a formula in Sheet2 C33 that picks and sums entries of a table on Sheet1 based on the following :

Sum the numbers in Sheet1!E6:E1006 (containing quantities of individual orders)
IF
1. the corresponding cells' entries in Sheet1!F6:F1006 (which are Required-on-Date entries,) are smaller than D31
AND
2. the corresponding cells' entries in Sheet1!F6:F1006 are greater or equal than C31
AND
3. the corresponding cells' entries in Sheet1!N6:N1006 in Not Empty

Entry in D31 : Date, the first of Month A+1
Entry in E31 : Date, the first of Month A

Paraphrasing:
Count how many parts do I have to ship per month, month by month.

So this is to calculate a monthly shipping demand figure and auto visualize in a graph.
The quantities and the status vary day-by-day an my desire is to have a dinamically updated graph.

I am sure some of you have had this already.

Cheers,
Gabor

[Paul]

I would like to have a formula in Sheet2 C33 that picks and sums entries of a table on Sheet1 based on the following :

Sum the numbers in Sheet1!E6:E1006 (containing quantities of individual orders)
IF
1. the corresponding cells' entries in Sheet1!F6:F1006 (which are Required-on-Date entries,) are smaller than D31
AND
2. the corresponding cells' entries in Sheet1!F6:F1006 are greater or equal than C31
AND
3. the corresponding cells' entries in Sheet1!N6:N1006 in Not Empty

Entry in D31 : Date, the first of Month A+1
Entry in E31 : Date, the first of Month A

How about something like:

=SUMPRODUCT((Sheet1!F6:F1006<D31)*(Sheet1!F6:F1006>=C31)*(Sheet1!N6:N1006<>"")*(Sheet1!E6:E1006))

[Gabor]

Thanks

I'm getting #VALUE!
It is due to the last section, *(Sheet1!E6:E1006)) since I delete this I get the number of records that fulfil the criterias.
But somehow it doesn't add up the values of Col E

Gabor

[Paul]

Instead of ending with:

*(Sheet1!E6:E1006))

Try this:

,(Sheet1!E6:E1006))

If that doesn't help, can you post a zipped sheet with some test data?

[duane]

sounds like there is text in that area so the change above should fix it

[Gabor]

YOU GOT IT.

pjoaquin and duane,

There were a couple of text entries.
Now the originally suggested formula WORKS PREMIUM.

Thanks a lot, you've made my day buddies.

Gabor

[Paul]

Glad to hear it worked. Thanks for the feedback.

[Gabor]

I wish I could have you guys on a BBQ one Saturday.
But the main thing I learned something again.
Have fun and prosper.
Gabor