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),G15:G17))

Please help.
Thanks
Bexi

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),G15:G17))
>
> Please help.
> Thanks
> Bexi
>

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