Need help solving this sumif scenario

Hello All,

I'm having a difficult time trying to write a formula that solve this scenario. I want to sum up certain task columns (TK1, TK2, TK3, etc) that has a weighted value. I also like to incorporate an if type statement from Type column as a criteria (like if Type = "NB") then add the task columns value. Each task column has a status (OUT, PEND, COMP). Depending on the task the value assigned will be different. Please see attached file for sampling and result wanted. I'm not sure how best to approach this scenario.



Thank you in advance.

I think I understand the results for rows 2-4:

OUT+COMP+PEND+COMP = 0+1+.5+1 = 2.5
PEND+PEND+COMP+OUT = .5+.5+1+0 = 2
COMP+PEND+COMP+PEND = 1+.5+1+.5 = 3

But why are rows 5-7 the "ELSE=TYPENAME" result? Why aren't they:

OUT+OUT+OUT+OUT = 0+0+0+0 = 0
PEND+COMP+COMP+OUT = .5+1+1+0 = 2
COMP+OUT+COMP+PEND = 1+0+1+.5 = 2.5

?

Without knowing the other conditions, something like this fits your results:

Formula:

Please Login or Register  to view this content.

Essentially, if A2 is NB, DP or RG, then count the number of COMP's and add the number of PEND's divided by 2. Since OUT's are 0, no need to count or sum them in the example given.

@John

Im getting a spill error with this formula?

@Paul

Hi Paul,

Thank you for the formula. It does work; however, I apologize for leaving out some crucial information that might render this formula ineffective. Each task column has a status (OUT, PEND, COMP). Depending on the task the value assigned will be different, but will always add up to 100 if all tasks were to be completed. Please see attached revised Excel sheet for a clearer defined parameter.

	A	B	C	D	E	F	G	H	I	J
1	Type	TK1	TK2	TK3	TK4	RESULT WANTED			TK1OUT	0
2	NB	OUT	COMP	PEND	COMP	60			TK2OUT	0
3	DP	PEND	PEND	COMP	OUT	65			TK3EOUT	0
4	RG	COMP	COMP	COMP	COMP	100			TK4OUT	0
5	AP	OUT	OUT	OUT	OUT	AP			TK1PEND	10
6	ZZ	PEND	COMP	COMP	OUT	ZZ			TK2PEND	15
7	CP	COMP	OUT	COMP	PEND	CP			TK3PEND	20
8									TK4PEND	5
9									TK1COMP	20
10									TK2COMP	30
11									TK3COMP	40
12									TK4COMP	10

F2=IF(ROWS($B$2:B2)<=3,SUMPRODUCT(SUMIF($I$1:$I$12,Table2[[#Headers],[TK1]:[TK4]]&Table2[@[TK1]:[TK4]],$J$1:$J$12)),[@Type])


Copy down

Please try..

By create listting table at G13:I28
TK1, Out, 0 ... and so on.

then (2 formula)

1. =IF(OR(A2="NB",A2="DP",A2="RG"),SUM(SUMIFS($I$13:$I$28,$H$13:$H$28,TRANSPOSE(Table23[@[TK1]:[TK4]]),$G$13:$G$28,TRANSPOSE(Table23[[#Headers],[TK1]:[TK4]]))),A2)
follow with Ctrl-shift-enter for array formula.

2. =IF(OR(A2="NB",A2="DP",A2="RG"),
SUMIFS($I$13:$I$28,$G$13:$G$28,Table23[[#Headers],[TK1]],$H$13:$H$28,Table23[@TK1])
+SUMIFS($I$13:$I$28,$G$13:$G$28,Table23[[#Headers],[TK2]],$H$13:$H$28,Table23[@TK2])
+SUMIFS($I$13:$I$28,$G$13:$G$28,Table23[[#Headers],[TK3]],$H$13:$H$28,Table23[@TK3])
+SUMIFS($I$13:$I$28,$G$13:$G$28,Table23[[#Headers],[TK4]],$H$13:$H$28,Table23[@TK4]),A2)


Regards.

For Post #14

If you don't mind to restructure your worksheet.
I change data table into one single table (format as range not table).

Then I use
=SUM(IFERROR(SUMIFS(OFFSET($D$2,1,MATCH($B33,$E$2:$H$2,0),ROWS($D$3:$D$23)),$D$3:$D$23,TRANSPOSE($D$32:$X$32)),1)*(SUMIF($J$3:$J$7,TRANSPOSE($D33:$X33),$K$3:$K$7)))
Follow with Ctrl-shift-enter for array.

Regards.

Note: In case column B is not in the list, it's will use COMP as 1 and PEND as 0.5

My question: Colud you please tell me , why the last DPIS result is 47 not 42.

"Note: In case column B is not in the list, it's will use COMP as 1 and PEND as 0.5"

meaning is ....

if the data in column B (for sample B33:B41) not found in the list of E2:H2 it will remains as 1
and multiply with the value lookuped from J3:K7
=SUM(IFERROR(SUMIFS(OFFSET($D$2,1,MATCH($B33,$E$2:$H$2,0),ROWS($D$3:$D$23)),$D$3:$D$23,TRANSPOSE($D$32:$X$32)),1)*(SUMIF($J$3:$J$7,TRANSPOSE($D33:$X33),$K$3:$K$7)))

Regards.

Originally Posted by menem

For Post #14

If you don't mind to restructure your worksheet.
I change data table into one single table (format as range not table).

Then I use
=SUM(IFERROR(SUMIFS(OFFSET($D$2,1,MATCH($B33,$E$2:$H$2,0),ROWS($D$3:$D$23)),$D$3:$D$23,TRANSPOSE($D$32:$X$32)),1)*(SUMIF($J$3:$J$7,TRANSPOSE($D33:$X33),$K$3:$K$7)))
Follow with Ctrl-shift-enter for array.

Regards.

Note: In case column B is not in the list, it's will use COMP as 1 and PEND as 0.5

My question: Colud you please tell me , why the last DPIS result is 47 not 42.

How do i change the result number as general to percentage? When I format the general number to percentage it went into the thousands.

Example: Result show 10, but when I switch to Percentage it shows 1000.00%. I want it to show 10%. Anyway to correct the formatting?

SOLVED: I changed the helper numbers to decimal point and it solved it.