=SUMIF with 2 Variables

[Skipan]

Hello everyone. I'm new here and I just have one question for now:

I have a Datasheet to control my sales. It has the cols:
DATE | PRODUCT | VALUE | FORM OF PAYMENT | CLIENT

I often use "SUMIF" to generate reports such as: "How many products X did I sell until now?"

Now I'm trying to create a report that will allow me to control the way I receive the money (Form of Payment). I can use SUMIF to calculate "How much did I receive in Cash until now?". But I need to answer "How much did I receive in Cash in May 5?".

I don't know how to put that into a formula that I can drag around and automatically have the answers.

I came close to the answer using DSUM, but it only allows me to insert some data and have one answer, and not having a full report of Form of Payment x Value x Date.

I imagine a formula like =SUMIF(Sales.$A$2:$A$60000 AND $D$2:$D$60000;"Cash" AND "05/05/2010";$E$2:$E$60000). I know that doesn't work.

Can anyone help me? I don't know VBA yet, and I need this working before I can learn it.

Thank you very very much!

[teylyn]

Hello Skipan,

welcome to the forum. Excel 2007 has a new function, SUMIFS(), that takes several conditions.

If I interpret your description correctly, something like this might work:

=SUMIFS($E$2:$E$60000;$A$2:$A$60000;"Cash";$D$2:$D$60000;"05/05/2010")

cheers

[Skipan]

That seems great! I'm trying to make this work here and maybe I can sleep again hahha!
Thank you so much for your help!

Just out of curiosity: is thee any other way to do that without SUMIFS? Like if I'm using OpenOffice or something?
Actually I'm checking this out right now, and it's said that SUMPRODUCT works the same way in Open Office, do you know this formula?

[teylyn]

=SUMIFS($E$2:$E$60000;$A$2:$A$60000;"Cash";$D$2:$D$60000;"05/05/2010")

translated into Sumproduct

=SUMPRODUCT(--($A$2:$A$60000="Cash");--($D$2:$D$60000;DATEVALUE("05/05/2010"));$E$2:$E$60000)

[NBVC]

There seems to be a bigger trend towards OO... this is the umpteenth post I have seen in the last few weeks, where the ultimate need is for a formula to work in OO...

[Skipan]

Yeah, many offices and people are using OO, I use Excel but it's good to have the knowledge since I have to deal with OO too!!
Thank you so much teylyn!