Sum a range of cells depending on conditions across two worksheets and lookup values

[Shardey]

Hi. So there are two sheets with me.
Sheet 1
Column A : Item No. (Unique nos.)
Column B: Quantity
Column C: approval status given by 3, 2, 1
Column D: (result column)

Sheet 2
Column A: Item No. (Unique nos. From column A sheet 1 are repeated numerous times here)
Column B: Quantity delivered
Column C: receipt status (two values - 'issued', 'pending')

Now, i need a formula to be put in column D (sheet 1), where, it checks, against unique value in column A, say cell A3, if the value of corresponding cell in column C, cell C3=3, if true, then lookups A3(sheet 1), in column A in sheet 2, whereever it finds it, its checks its corresponding column C value. If its 'issued' it then sums the value of corresponding column B (sheet 2) with all such values against that unique item no. And issued status.

Kindly help.

 

[nageswararao]

please post sample workbook

 

[Shardey]

Cannot seem to attach the excel directly. So added sample images.

 

[Shardey]

Also i cannot convert it into tables due to other restraints.

 

[Shardey]

please post sample workbook

Posted

 

[Fluff]

How about

=IF(C2=3,SUMIFS(Sheet2!B:B,Sheet2!A:A,A2,Sheet2!C:C,"Issued"),"")

 

[Shardey]

How about

=IF(C2=3,SUMIFS(Sheet2!B:B,Sheet2!A:A,A2,Sheet2!C:C,"Issued"),"")

Thank you

 

[Fluff]

Glad to help & thanks for the feedback.