Somewhat Complex Sumifs Formula

[DPChristman]

I think my formula writing is rusty, because I have been racking my brain all morning with this

I have a list of Approximately 1,200 retail locations that I am working with.

I am trying to total sales and loss in these stores based on the following criteria:

Operational Alerts Received is less than 33 (on the flip side, greater than 32), and alert compliance greater than 80.99999% (on the flip side, less than 81%)

I have done this type of formula before, but I think my brain is mush

 

[etaf]

not sure what column you are using, also what does FLIP SIDE mean, separate calculation

Not sure on SALES & LOSSES - where are these recorded

=SUMIFS( SUM_RANGE, Criteria, column with OPERATION Alerts in , "<"&33 , Criteria column with alert compliance, ">"&0.8099999 )

=SUMIFS( SUM_RANGE, Criteria, column with OPERATION Alerts in , ">"&32 , Criteria column with alert compliance, "<"&0.81 )

 

[Toadstool]

I'm also not sure if I'm following but here's an attempt.

DPChristman.xlsx
	A	B	C	D	E	F	G	H	I
1	Company	Sales	Loss	Operational Alert	Alert Compliance			Sales	Loss
2	ACME	22000	12000	33	80%		Op. Alerts < 33, Compliance >= 81	153000	30000
3	Bodgit	33000	13000	32	81%		Op. Alerts >= 33, Compliance < 81	77000	27000
4	Whitman	44000	14000	44	90%
5	Tensor	55000	15000	55	75%
6	Peachtree	66000	16000	12	75%
7	SumpCatch	120000	17000	12	82%
8
Sheet1

Cell Formulas
Range	Formula
H2:I2	H2	=SUMIFS(B$2:B$9999,$D$2:$D$9999,"<33",$E$2:$E$9999,">=81%")
H3:I3	H3	=SUMIFS(B$2:B$9999,$D$2:$D$9999,">=33",$E$2:$E$9999,"<81%")

 

[DPChristman]

not sure what column you are using, also what does FLIP SIDE mean, separate calculation

Not sure on SALES & LOSSES - where are these recorded

=SUMIFS( SUM_RANGE, Criteria, column with OPERATION Alerts in , "<"&33 , Criteria column with alert compliance, ">"&0.8099999 )

=SUMIFS( SUM_RANGE, Criteria, column with OPERATION Alerts in , ">"&32 , Criteria column with alert compliance, "<"&0.81 )

not sure what column you are using, also what does FLIP SIDE mean, separate calculation

Not sure on SALES & LOSSES - where are these recorded

=SUMIFS( SUM_RANGE, Criteria, column with OPERATION Alerts in , "<"&33 , Criteria column with alert compliance, ">"&0.8099999 )

=SUMIFS( SUM_RANGE, Criteria, column with OPERATION Alerts in , ">"&32 , Criteria column with alert compliance, "<"&0.81 )

 

[DPChristman]

Reworded and relabeled to make it easier to follow. What I am looking for is a way to show that if a store (Column A) had more than the company average of 32 alerts (Column C), and Alert Compliance (Column E) was below 81% (comp Avg), how did that impact the store's shrinkage based on the sales and shrink column (I&J).

On the same note, what was the affect in stores with less that 33 alerts, and 81% or better compliance. (image attached)

 

[etaf]

Sorry, I'm still not following
can you give some examples of results expected , and why

how many rows per company ? Just 1
If just 1 row, then not sure why SUMIFS

how did that impact the store's shrinkage based on the sales and shrink column (I&J).

What do you want to know from I & J ?
They are just numbers , so how do you Rate/measure Impact ?
Below the Companies Average - Above ?
OR what

 

[DPChristman]

Overall what I need to accomplish is, if a group of stores has higher than average alerts, and lower than average compliance, what is the combined sales / shrink
The other side of that is that if a group of stores has less than average number of alerts, and better than average compliance, what is the combine sales / shrink
Ideally, I want to show that if the stores (in general) have less than average alerts generated by transactions, and were more compliant in dealing with these alerts, they had a better shrink result.