Multiple criteria use in SUMIF

**chamdan** · 07-09-2007, 09:49 AM

The objective is to use the SUMIF Function to add all transactions for, which the Supplier Name is: "AAAA" if the date of transaction is between Apr/01/2007 and Jun/30/2007

Invoice Invoice Invoice
Date Number Supplier Total
Mar/02/2007 xxx Abc $100
Mar/10/2007 xxxx AAAA $100
Mar/30/2007 xxxxxxx BBBB $100
Apr/01/2007 xxxxxx AAAA $100
May/10/2007 xxxxxx AAAA $100
Jun/15/2007 xxxx BBBB $100
Jun/20/2007 xxxx AAAA $100
Jun/30/2007 xxxxxxxx BBBB $100
Jul/01/2007 xxxxxxxx BBBB $100

I have tried so many times with no success as I used the SUMIF function in a simplified way but once I had
add one more logical condition it did not work. Can any one out there help me achieve this?

Thank you in advance!
Chamdan

**dominicb** · 07-09-2007, 10:06 AM

Good afternoon chamdan

...and welcome to the forum!!

The bad news is you can't do this using SumIf

The good news is you can use SumProduct instead

This formula should help you :

=SUMPRODUCT(--(B1:B100="AAAA"),--(A1:A100>=DATEVALUE("01/04/07")),--(A1:A100<=DATEVALUE("30/06/07")),(C1:C100))

If you look at this and find the syntax a little bit awkward, my add-in - available from the link below - has a utility that will generate this type of formula using a wizard type of interface. Once installed just go to Ultimate > Formulae > Multiple SumIf Generator.

HTH

DominicB

**chamdan** · 07-09-2007, 10:27 AM

Thanks DominicB!
I tried it but it is giving me an error: #VALUE ??? on the other hand I was wondering why is there "--" between the two parenthesis???

Thanks in advance.

Chamdan

**chamdan** · 07-09-2007, 10:39 AM

Dominicb:

I found why it is giving me a "Value" error. The Datevalue("dd/mm/yy"), I changed it to Datevalue("mm/dd/yy") and the error is no longer showing but wondering what is these (2) two "--" for?

Thanks!

**dominicb** · 07-09-2007, 11:25 AM

Hi chamdan

I changed it to Datevalue("mm/dd/yy")

Ahaaa, well I'm English so I use dd/mm/yy (ie, the correct way)

what is these (2) two "--" for?

It's known as the "unary minus" and is used to coerce a True / False (Boolean) value to a numeric value. I believe that Harlan Grove was the first to start using the unary in this way. A more detailed explanation can be found here :

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

HTH

DominicB

**chamdan** · 07-09-2007, 02:44 PM

Thanks Dominicb for the tips!

My best regards from Canada

Chamdan

**chamdan** · 07-09-2007, 02:57 PM

Dominicb:

Can you see why am I getting zero ($0.0)? I could not figure out why?

See the enclosed example.

Regards,

Chamdan

**dominicb** · 07-09-2007, 04:01 PM

Hi chamdan

Can you see why am I getting zero ($0.0)?

I think you've worked hard enough today, matey.
Tell your boss I said you can go home!

Your column B, all the dates are for the year 2004. Your formula stipulates it returns results from the year 2007. The formula's right I'm afraid...!

HTH

DominicB