How to between dates?

[lopes_andre]

Hi

I have to calculate the profits sales by month. I don't know how to do it.


Sheet Example:

A1 | B1 | C1 | D1 | E1 | F1 |
Item | Buy Date | Sell Date | Buy Price | Sell Price | Profits |
01 | 01-01-2007 | 05-02-2007 | 500 | 700 | 200 |
02 | 05-01-2007 | 22-01-2007 | 100 | 120 | 20 |


Now I want to know the value of the profits by month(If item is sell on month 02, the value of earning will be on month 02(February).

G1 | H1 | I1 | J1 |
NO VALUE | Jan | Feb | Mar |
Profits | ?? | ?? | ?? |

How to calculate this?

I have the formula in my head, but I don't know how to write in excel language...

For Jan -> IF SellDate Between(01-01-2007 and 31-01-2007); SUM(F2:F500)

If anyone can help me to build a valid formula for this... many thanks.



Sorry for my bad english.
Best Regards, Andre.

[dominicb]

Good afternoon lopes_andre

...and welcome to the forum!!

Assuming that your sell date is sat in the range C1:C10 and your sell price is sat in E1:E10, use this formula :

=SUMPRODUCT(--(C1:C10>=DATEVALUE("01/01/07")),--(C1:C10<=DATEVALUE("31/01/07")),(E1:E10))

HTH

DominicB

[lopes_andre]

Hi Dominicb,

Many thanks for your answer.

I'am working with Google Spreadsheet, the formula gives me an error.

=SUMPRODUCT(--(Carteira!Q3:Q11>=DATEVALUE("01/01/07")),--(Carteira!Q3:Q11<=DATEVALUE("31/01/07")),(Carteira!L3:L11))

Q3:Q11 the range of Sell Date and L3:L11 the range of Profit.

The error in google spreadsheet is "#ERROR, Parse error"

One more question, what is the meaning of (--) in the formula??

I will try to install excel 2003

Thanks for help.

Best Regards, André.

[dominicb]

Hi lopes_andre

I'm not really surprised that this formula doesn't work on a Google speradsheet,as we're using a trick (using the unary (--) negative) to force the formula into doing something it wasn't originally designed to do. If you can't get hold of Excel you'll have to see if there is a Google spreadsheet forum.

Sorry for my bad english

.
Don't worry about your English here - you speak English better than I could your native language. However, you might struggle to follow the explanation given here on the unary negative (--), if you can't understand it, don't worry, just accept that it works!

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://www.mcgimpsey.com/excel/formulae/doubleneg.html

HTH

DominicB

[lopes_andre]

One more time, thanks for help.

Hi have tested an alternate formula in Google Spreadsheet that works (but only for the first line)

=SUMPRODUCT((Carteira!Q3:Q11>=DATEVALUE("01-Feb-2007"))*(Carteira!Q3:Q11<=DATEVALUE("28-Feb-2007"))*(Carteira!L3:L11))

This formula only returns the value of the first profit for sells on month 02(Feb)...

I have read the links about sumproduct, this formula should be compatible... Do you think is google spreadsheet problem?


Best Regards, André.

[dominicb]

Hi

Do you think is google spreadsheet problem?

I do think it's a Google Spreadsheet issue - not necessarily a problem, because as I said we'er not really using SUMPRODUCT as it's intended to be used - merely taking advantage of the way it works to calculate a sum based on criteria. This is what the Excel help system says about SUMPRODUCT :

Multiplies corresponding components in the given arrays, and returns the sum of those products.

Syntax

SUMPRODUCT(array1,array2,array3, ...)

Array1, array2, array3, ... are 2 to 30 arrays whose components you want to multiply and then add.

Remarks

The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.
SUMPRODUCT treats array entries that are not numeric as if they were zeros.

HTH

DominicB