Sumifs

[saru5133]

Please find attached the excel sheet i am trying to working on.
I am trying to get the sum of Sum Usage Qty from Data sheet under various contracts for various week ending dates.

Could you help me please.

[HansV]

Try this in D14:

=SUMPRODUCT((Data!$A$2:$A$842=$B$14)*(Data!$K$2:$K$842=D$13)*(Data!$L$2:$L$842=$C14)*Data!$N$2:$N$842)

You can fill this formula down to D21 and then right to column Q.

[saru5133]

OK,
But the result is if i apply that condition in D14 is 18072.0.
It should be only 18.

[HansV]

Sorry, I summed the wrong column. It should have been column M (Sum Usage Qty) instead of column N (Sum Tot Chrg Amt):

=SUMPRODUCT((Data!$A$2:$A$842=$B$14)*(Data!$K$2:$K$842=D$13)*(Data!$L$2:$L$842=$C14)*Data!$M$2:$M$842)

[saru5133]

Excellent... It is working.
But also wanted to know if this works with sumifs function also please.

[HansV]

I'm on a computer with Excel 2003 at the moment, and it'll be several hours before I can test in Excel 2007.

[HansV]

Try this formula:

=SUMIFS(Data!$M$2:$M$842,Data!$A$2:$A$842,$B$14,Data!$K$2:$K$842,D$13,Data!$L$2:$L$842,$C14)

It can be filled down and to the right.

[saru5133]

Thanks Hans,
This is one place i look for and confident about the solution you provide even when i have hands around to give suggestions.

Thanks once again....