SUMIF Forumula

[msalas0308]

I am having a brain fart. I know this is a simple formula but for some reason, I cannot get it. I have a sheet that has multiple information. I want to be able to do a sumif column "A (Period)" has 2021.01 and column "H (Ops Work Center)" has RVR101MA, RVR102MA, RVR103MA, RVR104MA, then sum column "S (SNAP&Conf Hours)".

(BTW, I am trying to get a weighted average of the Snap&Conf Hours by Ops Work Center and Period.)

Period	Plant Section	Priority	Ops Work Center	Has Confirmation	In Snapshot	SNAP&Conf Hours	Unsched Hours	Sched Hours
2021.01	WBK	2	RVR211ST	0	1	0​	0​	8​
2021.01	WBK	2	RVR298MS	0	1	0​	0​	10​
2021.02	WBK	2	RVR160MM	0	1	0​	0​	4​
2021.02	WBK	2	RVR298MS	1	1	10​	0​	10​
2021.02	WBK	2	RVR211ST	1	1	8​	0​	8​
2021.04	WBK	2	RVR160MM	0	1	0​	0​	4​
2021.05	WBK	2	RVR160MM	1	1	4​	0​	4​
2021.02	SRU	2	RVR211ST	1	1	24​	0​	24​
2021.02	SRU	2	RVR300EL	1	0	0​	0​	0​
2021.02	RFY	1	RVR399IE	1	0	0​	0​	0​
2021.01	UK2	2	RVR300EL	1	0	0​	1​	0​
2021.01	MNT	3	RVR230WE	1	0	0​	0​	0​
2021.02	MNT	3	RVR398IE	1	0	0​	0​	0​
2021.01	WBK	1	RVR398IE	1	0	0​	0​	0​
2021.03	WWT	1	RVR300EL	0	1	0​	0​	30​
2021.05	WWT	3	RVR103MA	1	1	24​	0​	24​
2021.01	WBK	2	RVR220LB	1	1	18​	0​	18​
2021.01	WBK	2	RVR250EO	1	1	2​	0​	2​
2021.04	RFY	1	RVR398IE	1	0	0​	0​	0​
2021.01	SRU	2	RVR230WE	1	0	0​	28​	0​
2021.02	SRU	2	RVR230WE	1	0	0​	4​	0​
2021.02	SRU	2	RVR240IC	1	0	0​	2​	0​
2021.02	SRU	2	RVR230WE	1	0	0​	0​	0​
2021.02	SRU	2	RVR204MS	1	0	0​	6​	0​

 

[kweaver]

Are the "RVR..." an OR situation and where do you want the sum result?
Based on your sample, it looks like they're all 0, no?

 

[msalas0308]

Are the "RVR..." an OR situation and where do you want the sum result?

Yes, it is an OR situation (they are grouped together). And the results will be in a different tab.

 

[kweaver]

I think it's more of a sumproduct. See if this works. I messed with the data a bit.

Book1
	A	B	C					H	I	J									S	T	U	V	W	X
1	Period	Plant Section	Priority					Ops Work Center	Has Confirmation	In Snapshot									SNAP&Conf Hours	Unsched Hours	Sched Hours		Unique A
2	2021.01	WBK	2					RVR211ST	0	1									0	0	8		2021.01	110
3	2021.01	WBK	2					RVR103MA	0	1									10	0	10		2021.02	324
4	2021.02	WBK	2					RVR160MM	0	1									0	0	4		2021.04	0
5	2021.02	WBK	2					RVR298MS	1	1									10	0	10		2021.05	24
6	2021.02	WBK	2					RVR211ST	1	1									8	0	8		2021.03	0
7	2021.04	WBK	2					RVR160MM	0	1									0	0	4
8	2021.05	WBK	2					RVR160MM	1	1									4	0	4
9	2021.02	SRU	2					RVR101MA	1	1									24	0	24
10	2021.02	SRU	2					RVR300EL	1	0									0	0	0
11	2021.02	RFY	1					RVR399IE	1	0									0	0	0
12	2021.01	UK2	2					RVR102MA	1	0									100	1	0
13	2021.01	MNT	3					RVR230WE	1	0									0	0	0
14	2021.02	MNT	3					RVR398IE	1	0									0	0	0
15	2021.01	WBK	1					RVR398IE	1	0									0	0	0
16	2021.03	WWT	1					RVR300EL	0	1									0	0	30
17	2021.05	WWT	3					RVR103MA	1	1									24	0	24
18	2021.01	WBK	2					RVR220LB	1	1									18	0	18
19	2021.01	WBK	2					RVR250EO	1	1									2	0	2
20	2021.04	RFY	1					RVR398IE	1	0									0	0	0
21	2021.01	SRU	2					RVR230WE	1	0									0	28	0
22	2021.02	SRU	2					RVR230WE	1	0									0	4	0
23	2021.02	SRU	2					RVR101MA	1	0									200	2	0
24	2021.02	SRU	2					RVR230WE	1	0									0	0	0
25	2021.02	SRU	2					RVR104MA	1	0									100	6	0
Sheet1

Cell Formulas
Range	Formula
W2:W6	W2	=UNIQUE(A2:A25)
X2:X6	X2	=SUMPRODUCT(($S$2:$S$25)*(W2=$A$2:$A$25)*(($H$2:$H$25="RVR101MA")+($H$2:$H$25="RVR102MA")+($H$2:$H$25="RVR103MA")+($H$2:$H$25="RVR104MA")))
Dynamic array formulas.

 

[Rahul1987]

I am having a brain fart. I know this is a simple formula but for some reason, I cannot get it. I have a sheet that has multiple information. I want to be able to do a sumif column "A (Period)" has 2021.01 and column "H (Ops Work Center)" has RVR101MA, RVR102MA, RVR103MA, RVR104MA, then sum column "S (SNAP&Conf Hours)".

(BTW, I am trying to get a weighted average of the Snap&Conf Hours by Ops Work Center and Period.)

Period	Plant Section	Priority	Ops Work Center	Has Confirmation	In Snapshot	SNAP&Conf Hours	Unsched Hours	Sched Hours
2021.01	WBK	2	RVR211ST	0	1	0​	0​	8​
2021.01	WBK	2	RVR298MS	0	1	0​	0​	10​
2021.02	WBK	2	RVR160MM	0	1	0​	0​	4​
2021.02	WBK	2	RVR298MS	1	1	10​	0​	10​
2021.02	WBK	2	RVR211ST	1	1	8​	0​	8​
2021.04	WBK	2	RVR160MM	0	1	0​	0​	4​
2021.05	WBK	2	RVR160MM	1	1	4​	0​	4​
2021.02	SRU	2	RVR211ST	1	1	24​	0​	24​
2021.02	SRU	2	RVR300EL	1	0	0​	0​	0​
2021.02	RFY	1	RVR399IE	1	0	0​	0​	0​
2021.01	UK2	2	RVR300EL	1	0	0​	1​	0​
2021.01	MNT	3	RVR230WE	1	0	0​	0​	0​
2021.02	MNT	3	RVR398IE	1	0	0​	0​	0​
2021.01	WBK	1	RVR398IE	1	0	0​	0​	0​
2021.03	WWT	1	RVR300EL	0	1	0​	0​	30​
2021.05	WWT	3	RVR103MA	1	1	24​	0​	24​
2021.01	WBK	2	RVR220LB	1	1	18​	0​	18​
2021.01	WBK	2	RVR250EO	1	1	2​	0​	2​
2021.04	RFY	1	RVR398IE	1	0	0​	0​	0​
2021.01	SRU	2	RVR230WE	1	0	0​	28​	0​
2021.02	SRU	2	RVR230WE	1	0	0​	4​	0​
2021.02	SRU	2	RVR240IC	1	0	0​	2​	0​
2021.02	SRU	2	RVR230WE	1	0	0​	0​	0​
2021.02	SRU	2	RVR204MS	1	0	0​	6​	0​

You have given two factors Period & Ops Work Center and you want to calculate weighted avg cost ??