Calculate the total values across a row of a work schedule, based on specific conditions

I am facing a challenge with my work schedule data, as it is laid out horizontally. I am trying to calculate the total hours in the schedule based on various criteria such as the person's name, their available hours, shift, and the machine they are assigned to. The layout of the data can be seen in the attached picture.

My goal is to sum the total hours by machine, shift, and week number. For example, if the shift is 1, the machine is 300, and the week number is 40 (top row), the total should be 8 hours.

I have attempted using regular sumifs, experimented with sumproduct which I am not very familiar with, tried working with all columns vs just one column, and even created named ranges, but I am still encountering errors. I do not have much experience with arrays, but I thought that might be a solution too.

https://i.sstatic.net/0Gp3g.jpg

Any assistance would be highly appreciated! I am currently learning TypeScript, so if implementing that could offer a solution, I am open to exploring that option as well.

Thanks!!!

arraysexcelvbatypescriptexcel-formula

Answer №1

To tackle the complexity of your layout, you'll need to create a rather intricate equation. If you happen to have Excel 365, the process becomes much more straightforward.

Start by forming an array that encompasses your entire table. Then proceed to establish mask arrays and sum as necessary.

As an example, consider this array containing all values:

=IF(ISNUMBER(A1:Z26),A1:Z26,0)

To generate a mask that isolates only week 40, utilize the following formula:

=IF(A1:Z26=40,ISNUMBER(A1:Z26),0)

Filtering by shift and machine can present challenges since you're sorting by row while the relevant data is located in another row. Here's how to address it for shift 1:

=IF(OFFSET(A1:Z26,1,0)=1,ISNUMBER(A1:Z26),0)

The offset adjustment is required for differentiating by machine. To do so for machine 300, implement this formula:

=IF(OFFSET(A1:Z26,2,0)=300,ISNUMBER(A1:Z26),0)

Next, multiply all these components together. This action will yield an array containing values from your spreadsheet with excluded elements set to zero. Finally, sum up the resulting array:

=SUM(IF(ISNUMBER(A1:Z26),A1:Z26,0) * IF(A1:Z26=40,ISNUMBER(A1:Z26),0) * IF(OFFSET(A1:Z26,1,0)=1,ISNUMBER(A1:Z26),0) * IF(OFFSET(A1:Z26,2,0)=300,ISNUMBER(A1:Z26),0))

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Error during Next.js build: Incompatible types - cannot assign type to 'never'

Encountering an error in the console while attempting to build my project: .next/types/app/facebook/page.ts:8:13 Type error: Type 'OmitWithTag<typeof import("D:/projects/abkh24/client/app/facebook/page"), "metadata" | "defa ...

reactjstypescriptfacebook-graph-apinext.jseslint

What is the best way to incorporate a TypeScript function within a JQuery function?

I'm facing an issue with installing the Angular Instascan library, so I am using it without installing and only importing the script. In order to make it work, I have to use JQuery in my TypeScript file within the component. Is there a way to call a T ...

jqueryangulartypescriptinstascan

Guide on creating a 4-point perspective transform with HTML5 canvas and three.js

First off, here's a visual representation of my objective: https://i.stack.imgur.com/5Uo1h.png (Credit for the photo: ) The concise question How can I use HTML5 video & canvas to execute a 4-point perspective transform in order to display only ...

javascripttypescriptcanvasthree.jslinear-algebra

A function that checks if all numbers from 0 to n-1 are present in the given array, having a time complexity of O(n) and returning either 0 or 1

UPDATE: I accidentally omitted that I do not wish to assign another temporary array. I am tackling a challenge in C, where I am tasked with: Given an array 'a' and its size 'N', it is known that all elements in the array fall within th ...

carrays

Utilizing Angular 2 for Integration of Google Calendar API

I recently attempted to integrate the Google Calendar API with Angular 2 in order to display upcoming events on a web application I am developing. Following the Google Calendar JavaScript quick-start tutorial, I successfully managed to set up the API, inse ...

javascriptangulartypescriptgoogle-apigoogle-calendar-api

What are the steps to effectively utilize npm warnings within tslint?

After using eslint for javascript files, I am now transitioning to tslint for TypeScript linting. With eslint, I could specify some errors as NPM warnings, such as console logging. https://i.sstatic.net/BNhy6.png I realize that my "warnings" are generat ...

node.jstypescriptnpmeslinttslint

Angular 4's OrderBy Directive for Sorting Results

I've been working on implementing a sorting pipe based on the code provided in this resource: The issue I'm facing revolves around handling undefined values within my data. The sorting pipe functions correctly when there are no undefined values ...

angularsortingtypescript

The incorrect starting points for nested for loops

I'm facing an issue while utilizing a nested for loop to generate x and y coordinates for a method call. Strangely, the loop variables seem to be starting off at incorrect values when I check using console.log. What could be the reason behind this une ...

node.jstypescriptfor-loopnestedelectron

Is there a way to order the execution of two functions that each produce promises?

With my code, I first check the status of word.statusId to see if it's dirty. If it is, I update the word and then proceed to update wordForms. If it's clean, I simply update wordForms. I'm looking for advice on whether this is the correct a ...

javascripttypescript

What is the result of using `X ? A : B` in typescript?

type TestAny = any extends 'a' ? 1 : 2 // => 1 | 2 why??? how to interpret? type TestUnknown = unknown extends 'a' ? 1 : 2 // => 2 type TestStringA = 'a' extends 'a' ? 1 : 2 // => 1 type SomeUnion = ' ...

typescript

When working with Angular 2 and Typescript, a common error message that may be encountered is "TypeError

Currently diving into Angular 2 and encountered a stumbling block while attempting to create a service. Despite my efforts in finding a solution, I seem to be missing something crucial. Error: A problem arises in angular2-polyfills.js:1243 with the error ...

javascriptangularjstypescriptangular

Encountering an "Undefined property" error in Angular when trying to read a property, even though the json-p

manager.ts export interface Manager { id: number; name: string; employees: Array<Employee>; } employee.ts export interface Employee { id: number; fullName: string; } managers.component.ts export class ManagersComponent implem ...

angulartypescript

Stuck on loading screen with Angular 2 Testing App

Currently working on creating a test app for Angular 2, but encountering an issue where my application is continuously stuck on the "Loading..." screen. Below are the various files involved: app.component.ts: import {Component} from '@angular/core& ...

javascriptangularjsnode.jstypescriptangular

Patience is key: Techniques for waiting on service responses in Angular 2

Here is the code snippet I have been working on: canActivate(route: ActivatedRouteSnapshot, state: RouterStateSnapshot): boolean { let isAuthenticated: boolean = false this.authServiceLocal.isAuthenticated().then(response => isAuthenticated = r ...

angulartypescript

The property "state" of RouteComponentProps location does not exist in the provided type {}

We recently encountered a new error that was not present before. Previously, our code compiled without any issues and the compilation process went smoothly. However, individuals who installed the react application from scratch are now facing an error speci ...

reactjstypescriptreact-router

What is the proper way to utilize variables in package.json with npm version 7.x.x?

I am looking to utilize npm scripts to access keys found in the directories section. "directories": { "client": "client", "server": "server" }, "scripts": { "test:client&qu ...

javascriptnode.jstypescriptnpm

Exploring the functionality of CanDeactiveGuard and ModalDialogService through unit testing

In my application, the CanDeactiveGuard is functioning properly. During unit testing, I encountered an issue with one test where I intended to use callThrough to invoke the openConfirmDialog() method within the Guard. This method triggers the Modal Dialog ...

angulartypescript

Struggling to populate a fresh JavaScript array with values submitted from a form

I am currently working on a form to collect Family information that will be used for enrolling in a new benefit plan. My goal is to populate an array with the necessary values for each individual to create the benefit record. While researching, I have only ...

javascriptarraysformscreationpopulation

Using a static enum in a different class in TypeScript: A guide

After referencing this question and answer on Stack Overflow about setting a static enum inside a TypeScript class, I decided to create my own enum and implement it as a static property in my class. Here is how I did it: /* Input.ts */ enum INPUT_TYPE { T ...

typescript

Changing spaces to plus signs within a PHP arrayOrConverting

I have retrieved an array called $allprofessions from the database Array ( [0] => Html css js [2] => web developer [3] => fitness trainer) I need to replace all spaces in the array ' ' with '+'. The desired output should be: ...

phparrays