Looking for some help with this, can not seem to get what I am looking for.

[GMCD1972]

Looking for some help with this, can not seem to get what I am looking for. I can not get it to calculate the >=

=SUM(SUMIFS(Inventory[Ext-Value],Inventory[Status],{"0"},Inventory[QOH],">="&Inventory[BSL]))

I'm getting the same total as...

=SUMIFS(Inventory[Ext-Value],Inventory[Status],{"0"})


Example of Table: Inventory[Status] = {"0","NS","AP",DP","RB","RBH","MO"}

Store

Make

Source

Part#

Description

QOH

QPR

BSL

Excess

YRSL

Bin

MNS

MNR

Status

Cost

Ext-Value

MfrDet

Return

LastRC

Min

MFRST

Days

TBSL

Entry

New Source

New Bin

Notes

43831

GM

3

10028352

RETAINER

4

0

0

3

B110G

8

31

NS

$3.75

$15.00

0Y CPOBK

R

11/7/2017

0

5

11/4/2017

5

 

[KRice]

Are you entering the formula as an array formula, confirmed with Ctrl-Shift-Enter? After doing so, you should see curly braces appear around the formula in the formula bar.

 

[GMCD1972]

I did see Curly's appear after hitting Ctrl-Shift-Enter. Should that have changed anything?

 

[GMCD1972]

The table 'Inventory' is a little over 4900 Rows

 

[KRice]

It's not clear to me what are you trying to achieve, but I suspect it is closer to that shown in Result3 in the example below.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	Result1	45
2	Result2	360
3	Result3	30
4
5	Store	Make	Source	Part#	Description	QOH	QPR	BSL	Excess	YRSL	Bin	MNS	MNR	Status	Cost	Ext-Value	MfrDet	Return	LastRC	Min	MFRST	Days	TBSL	Entry	New Source	New Bin	Notes
6	43831	GM	3	10028352	RETAINER	4	0	0		3	B110G	8	31	NS	3.75	15	0Y CPOBK	R	###	0	5			###	5
7	43831	GM	3	10028352	RETAINER	4	0	0		3	B110G	8	31	0	3.75	15	0Y CPOBK	R	###	0	5			###	5
8	43831	GM	3	10028352	RETAINER	4	0	0		3	B110G	8	31	AP	3.75	15	0Y CPOBK	R	###	0	5			###	5
9	43831	GM	3	10028352	RETAINER	4	0	0		3	B110G	8	31	DP	3.75	15	0Y CPOBK	R	###	0	5			###	5
10	43831	GM	3	10028352	RETAINER	4	0	0		3	B110G	8	31	RB	3.75	15	0Y CPOBK	R	###	0	5			###	5
11	43831	GM	3	10028352	RETAINER	4	0	0		3	B110G	8	31	RBH	3.75	15	0Y CPOBK	R	###	0	5			###	5
12	43831	GM	3	10028352	RETAINER	4	0	0		3	B110G	8	31	MO	3.75	15	0Y CPOBK	R	###	0	5			###	5
13	43831	GM	3	10028352	RETAINER	4	0	0		3	B110G	8	31	0	3.75	15	0Y CPOBK	R	###	0	5			###	5
14	43831	GM	3	10028352	RETAINER	4	0	5		3	B110G	8	31	0	3.75	15	0Y CPOBK	R	###	0	5			###	5
15
Sheet1

Cell Formulas
Range	Formula
B1	B1	=SUMIFS(Inventory[Ext-Value],Inventory[Status],{"0"})
B2	B2	=SUM(SUMIFS(Inventory[Ext-Value],Inventory[Status],{"0"},Inventory[QOH],">="&Inventory[BSL]))
B3	B3	=SUMPRODUCT(Inventory[Ext-Value],(Inventory[Status]=0)*(Inventory[QOH]>=Inventory[BSL]))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[GMCD1972]

I'm sorry, That did not even cross my mind to type out what i was trying to achieve. I'm looking to total all Parts on hand, by [Ext-Value] that have a particular stocking status. In this case [Status] {"0"}. and that a Quantity on Hand [QOH] that is greater than or equal to the Best Stocking Level [BSL]. I know what my value should be, I just can not get this formula to calculate it.

 

[KRice]

Does the SUMPRODUCT formula shown as "Result3" do what you want? I'm assuming that you want a row-by-row comparision?...that is, if the QOH >= BSL on that row AND Status=0 on that same row, then include Ext-Value in the sum?

 

[GMCD1972]

I will have to look either when I get back to work or my pc at home hooked back up.

 

[GMCD1972]

Never got around to hooking PC back up, wife doing some painting around the house.

Those formula you post did not produce what i was looking for. Let me see if I can explain this some with this data listed

This it my formula:

=SUMIFS(Inventory[Ext-Value],Inventory[Status],{"0"},Inventory[QOH],">="&Inventory[BSL]) <--- this formula is ignoring the 3rd criteria.

I was expecting this result. $13.74 ------ 7th item down meets the criteria.

I get these results by adding [Ext-Value], if [Status] = {0}, and [QOH] >= [BSL]

I was actually able to get it to work with this formula from within another sheet with just throwing some numbers out there. =SUMIFS(P2:P8,N2:N8,{0},F2:F8,">=" &H2:H8)



Example of Table: Inventory[Status] = {"0","NS","AP",DP","RB","RBH","MO"}

Store	Make	Source	Part#	Description	QOH	QPR	BSL	Excess	YRSL	Bin	MNS	MNR	Status	Cost	Ext-Value	MfrDet	Return	LastRC	Min	MFRST	Days	TBSL	Entry	New Source	New Bin	Notes
43831	GM	3	10028352	RETAINER	4	0	0		3	B110G	8	31	NS	$3.75	$15.00	0Y CPOBK	R	11/7/2017	0	5			11/4/2017	5
43831	GM	1	10097530	CAP	4	0	0		0	042E	3	3	NS	$25.23	$100.92	0W WC	R	3/3/2020	0	9			2/24/2020	9
43831	GM	2	10121502	RETAINER	16	0	1		17	054B	5	6	0	$0.61	$9.76	0Y CPOBK	R	12/19/2019	0	2			10/24/1995	2
43831	GM	2	10154775	GASKET	2	0	0		2	B105K	5	5	AP	$15.19	$30.38	0Y CPOB	R	1/9/2020	0	4			3/2/1994	4
43831	GM	2	10166345	KEY	10	0	0		2	B105F	2	3	AP	$1.84	$18.40	0Y CPOBKB	R	3/19/2020	0	4			9/1/2016	4
43831	GM	3	10236330	WEATHERSTRIP	1	0	0		0	063H	58	58	AP	$134.89	$134.89	0W WC	0	8/7/2015	0	9			6/7/2015	9
43831	GM	2	10238852	ROD	1	0	1		1	B105G	4	4	0	$13.74	$13.74	0Y CP BKB	R	1/28/2020	0	2			9/1/2016	2
43831	GM	2	10306887	BLADE	1	0	0		1	142M	9	9	NS	$8.37	$8.37	0Y C	R	9/9/2019	0	5			8/4/2016	5
43831	GM	2	10306888	BLADE	1	0	0		1	142M	9	9	NS	$8.37	$8.37	0Y C	R	9/10/2019	0	5			8/4/2016	5

 

[djbe17]

I think this is what you're after - if you are not over confident with Excel - add some "helper rows" that's what I would do and then hide them away.
If not you can could use SUMPRODUCT to achieve this - this produces arrays with True/False (1/0s) which when multiplied will add just the highlighted cells.