Share via

using conditional sumproduct, trabspose

David Polsangi 20 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,731 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jiajing Hua-MFST 11,045 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.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.