SUMPRODUCT and calculating an amount

Hello,

Daddylonglegs and EdMac helped me on the following function recently!

=SUMPRODUCT(--(TEXT(Results!C1:C1500,0)="101"),--(TEXT(Results!V1:V1500,0)=B7),--(TEXT(Results!E1:E1500,"mmm-yy")="jul-07"))

The result of the calculation correctly counts how many milestones (101's) in a particular group, in a particular month.

Result = 4

I now want the sum from the 'amount' column which is 4 columns across from 101

Milestone Amount
101 10,000
101 20,000
101 20,000
101 50,000

Result = 100,000. This is the result I want rather than the count of 4 (given the other conditions).

Example 11 on 'Multiple Condition Tests' was useful... http://www.xldynamic.com/source/xld.SUMPRODUCT.html
However, I am not checking visible cells on data that uses a filter - It incoporates SUBTOTAL, ROW, INDEX and OFFSET. I wasnt sure what i needed. SUBTOTAL? OFFSET? I tried a few things but alas I need help again... my current example...

=SUMPRODUCT(--(OFFSET((TEXT('Results (4)'!C1:C1500,0)="101"))),--(TEXT(Results!V1:V1500,0)=B7),--(TEXT(Results!E1:E1500,"mmm-yy")="jul-07"))

Any suggestions or directions greatly appreciated!!

Kind regards,

Muchado77

Hi

Try adding the amount column to the existing formula

=SUMPRODUCT(--(TEXT(Results!C1:C1500,0)="101"),--(TEXT(Results!V1:V1500,0)=B7),--(TEXT(Results!E1:E1500,"mmm-yy")="jul-07"),(Results!G1:G1500))


rylo

Rylo

That is fantastic! It works just as it should.

You make it seem so easy, so obvious!

The world is such a lovely place when I visit this site!

Thanks so much!

Kindest Regards,

Muchado77