Hi
I have the follwing grid
A B C D E
F G
Facture PO Date Montant HT GST/TPS PST/TVQ Total
1 12345 Dec-07 100.00$ 6.00$ 7.95$ 113.95$
3 12346 Dec-07 1,500.00$ 90.00$ 119.25$ 1,709.25$

PO is consider as a string
Date is consider as date (mmm-yy)
Total is currency 2 decimal $ sign

PO & Date are from a validation list

These return 0 I was expected 113.95:

SUMPRODUCT((B5:B6="12345"),(C5:C6="2007/12/11"),G5:G6)
SUMPRODUCT(--(B5:B6="12345"),--(C5:C6="2007/12/11"),--G5:G6)
SUMPRODUCT((B5:B6="12345"),(C5:C6="2007/12/11"),--G5:G6)

Any ideas on how to fix this ?

Thanks,
Nde

Try this:

SUMPRODUCT((B5:B6="12345")*(C5:C6=--"2007/12/11")*G5:G6)


"Nde" wrote:

Hi
I have the follwing grid
A B C D E
F G
Facture PO Date Montant HT GST/TPS PST/TVQ Total
1 12345 Dec-07 100.00$ 6.00$ 7.95$ 113.95$
3 12346 Dec-07 1,500.00$ 90.00$ 119.25$ 1,709.25$

PO is consider as a string
Date is consider as date (mmm-yy)
Total is currency 2 decimal $ sign

PO & Date are from a validation list

These return 0 I was expected 113.95:

SUMPRODUCT((B5:B6="12345"),(C5:C6="2007/12/11"),G5:G6)
SUMPRODUCT(--(B5:B6="12345"),--(C5:C6="2007/12/11"),--G5:G6)
SUMPRODUCT((B5:B6="12345"),(C5:C6="2007/12/11"),--G5:G6)

Any ideas on how to fix this ?

Thanks,
Nde

One way:

=SUMPRODUCT(--(B5:B6="12345"),--(YEAR(C5:C6)=2007),
--(MONTH(C5:C6)=12), G5:G6)

In article ,
Nde wrote:

Hi
I have the follwing grid
A B C D E
F G
Facture PO Date Montant HT GST/TPS PST/TVQ Total
1 12345 Dec-07 100.00$ 6.00$ 7.95$ 113.95$
3 12346 Dec-07 1,500.00$ 90.00$ 119.25$ 1,709.25$

PO is consider as a string
Date is consider as date (mmm-yy)
Total is currency 2 decimal $ sign

PO & Date are from a validation list

These return 0 I was expected 113.95:

SUMPRODUCT((B5:B6="12345"),(C5:C6="2007/12/11"),G5:G6)
SUMPRODUCT(--(B5:B6="12345"),--(C5:C6="2007/12/11"),--G5:G6)
SUMPRODUCT((B5:B6="12345"),(C5:C6="2007/12/11"),--G5:G6)

Any ideas on how to fix this ?

Thanks,
Nde