Multiple Sumif across columns

[nikijon]

Hi

I was hoping someone could please help.

I have a list of Jobs with hours against each and these are spread across multiple columns for each day, with the Job potentially being different across the sheet.

I can create a simple sumif but was wondering if it was possible to repeat the formula but across multiple columns with the criteria column moving across also. I can do this by repeating the formula, but was looking to see if there was an easier way rather than using the method I have.

I realise that the format of the information may not be laid out the best, but this is using an existing table.

The formula I have at the moment is below and I have attached an example table =SUMIF(C4:C19,A32,D4:D19)+SUMIF(E4:E19,A32,F4:F19)+SUMIF(G4:G19,A32,H4:H19)+SUMIF(I4:I19,A32,J4:J19)+SUMIF(K4:K19,A32,L4:L19)+SUMIF(M4:M19,A32,N4:N19)+SUMIF(O4:O19,A32,P4:P19)+SUMIF(A4:A19,A32,B4:B19)

Any help would be greatly appreciated.

Thanks NIki

 

[Fluff]

How about

=LET(a,WRAPROWS(TOCOL(A4:P19),2),SUM(FILTER(INDEX(a,,2),INDEX(a,,1)=A32)))

 

[awoohaw]

You can also try this:

Book1
	A	B	C	D	E	F	G	H	I
1
2
3
4	Job1	4	Job3	3	Job1	1	Job3	3
5	Job2	1	Job1	3	Job2	3	Job1	1
6			Job1	3			Job1	4
7
8
9
10	Job1	16
11	Job2	4
12	Job3	6
13
Sheet2

Cell Formulas
Range	Formula
B10:B12	B10	=SUMPRODUCT(($A$4:$G$6=A10)*(IF(ISNUMBER($B$4:$H$6),$B$4:$H$6,0)))

 

[nikijon]

Hi Fluff & awoohaw

Thank you for your quick replies. Both solutions worked and I really appreciate your help with this (just need to understand the workings of the LET, WRAPROWS & TOCOL functions)

Thank You Niki

 

[Fluff]

Glad we could help & thanks for the feedback.