Share via

using conditional sumproduct, trabspose

David Polsangi 0 Reputation points
2024-06-17T06:05:40.3166667+00:00

Hi

I have a huge data base and need to sort and filter some resultsimage

I need to calculate " The labour" working hours in multiple tables of ONE SHEET, as above,

=IF(M319,SUMPRODUCT(--(A319:A327=TRANSPOSE(Labour_CC))G319:G327J319:J327*I319:I327),0)

Columns G=if the item is included

I= number of labours

J= hours

User's image

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,679 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jiajing Hua-MFST 7,145 Reputation points Microsoft Vendor
    2024-06-17T08:17:35.0833333+00:00

    Hi @David Polsangi

    Please check whether following formula is helpful.

    =IF(M319,SUMPRODUCT(--(ISNUMBER(MATCH(A319:A327,TRANSPOSE(Labour_CC))))(G319:G327=1),J319:J327I319:I327),0)

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.