Add amount if record contains a value
 

Hello,

I want to add the amount based on column A if the text in A contains
*88B and my data looks like this

Sheet1
A B C
AI 1088B AA886 2500
KA1088B AA886 3000
AAA1088B AA886 1000

Sheet2
A B C
AAA1088B AA886

The formula is in Sheet2 column C.

I used this formula but it returned FALSE
=IF(MID(A1,6,2)=MID(A1,6,2),SUMPRODUCT((A1:B17),G1 5:G17))

Please help.
Thanks
Bexi

Add amount if record contains a value
 

Hi Bexi

Try
=SUMPRODUCT(--(MID(Sheet1!$A$1:$A$17,6,3)="88B"),Sheet1!$C$1:$C$ 17)

or
=SUMPRODUCT(--(MID(Sheet1!$A$1:$A$17,6,3)=MID(A1,6,3)),Sheet1!$C $1:$C$17)


Regards

Roger Govier


Bexi wrote:
Hello,

I want to add the amount based on column A if the text in A contains
*88B and my data looks like this

Sheet1
A B C
AI 1088B AA886 2500
KA1088B AA886 3000
AAA1088B AA886 1000

Sheet2
A B C
AAA1088B AA886

The formula is in Sheet2 column C.

I used this formula but it returned FALSE
=IF(MID(A1,6,2)=MID(A1,6,2),SUMPRODUCT((A1:B17),G1 5:G17))

Please help.
Thanks
Bexi

Add amount if record contains a value
 

Hi Roger,

After I sent the email last night, I searched my formula in my groups
and I found my
solution. Below is the formula that I used, but I will replace "E7"
with ="88B".

=SUMPRODUCT(--(MID($A$12:$A$21,10,2)=E7)*(B12:B21="A"),$D$12:$D$ 21)

Thank you so much for your help.
Best regards,
Bexi