If / And and a Sum If Formula?

[Mississippi Girl]

Hi - been going in circles with this formula for a while.
I have 2 data sets; one is the base value of services/commodities, etc. with associated fees calculated in Rows 13 and 14; the second data set contains the same data, but at the fully loaded value (with fees calculated on each item vs. at the end).

Fee 1 is applied to everything, no matter what. That's an easy one.
Fee 2 is only applied to Contract Labor and Contract Materials, but only when the sum of Contract Labor and Contract Materials (in cell range O3:O11) is $1M or higher.

Row 15 should equal Row 28.
Can you see what I'm doing wrong? In my mind, this makes perfect sense.
Thanks for your help!

 

[Candyman8019]

The difference is that you don't have any Fee2 included in the second table calculations...which it should since O3:O11 is greater than $1M.

 

[Candyman8019]

Try something like this in D20: =if(and($A20="Contract*",sumifs($O$3:$O$11,$A$3:$A$11,"Contract*")>999999),D4+(D4*.0052)+(D4*.144),D4*1.144)
but without seeing the other formulas in your sheet it's difficult. Can you use the XL2BB add in from this site to share the range in your screenshot?

XL2BB - Excel Range to BBCode

Convert selected range to BBCode to include as a snapshot in your question.

www.mrexcel.com

 

[Mississippi Girl]

Try something like this in D20: =if(and($A20="Contract*",sumifs($O$3:$O$11,$A$3:$A$11,"Contract*")>999999),D4+(D4*.0052)+(D4*.144),D4*1.144)
but without seeing the other formulas in your sheet it's difficult. Can you use the XL2BB add in from this site to share the range in your screenshot?

XL2BB - Excel Range to BBCode

Convert selected range to BBCode to include as a snapshot in your question.

www.mrexcel.com

Stand by

 

[Mississippi Girl]

Stand by

Mr Excel Help.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Base Values
2	Resource	Category	Prior Funding Received	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Prior Funding Received	FY23	FY24	Total This Action	New Work Total
3	Direct Labor	Labor	146,418	58,899	13,760	20,296	20,296	20,296	20,296		146,418	153,843	-	153,843	300,261
4	Contract Labor	Labor	322,983	500,000	14,720	35,788	35,788	35,788	35,788		322,983	657,872	-	657,872	980,855
5	Contract Materials	Material	251,717		28,228						251,717	28,228	-	28,228	279,945
6	Commoditiy #1	Commodities									-	-	-	-	-
7	Commoditiy #2	Commodities	29,148								29,148	-	-	-	29,148
8	Commoditiy #3	Commodities	88,041								88,041	-	-	-	88,041
9	Commoditiy #4	Commodities			2,182	2,182	2,182	2,182	2,182		-	10,909	-	10,909	10,909
10	Commoditiy #5	Commodities			3,000						-	3,000	-	3,000	3,000
11	Commoditiy #6	Commodities									-	-	-	-	-
12			838,307	558,899	61,890	58,266	58,266	58,266	58,266	-	838,307	853,852	-	853,852	1,692,159
13	FEE 1 - 14.4%		120,716	80,481	8,912	8,390	8,390	8,390	8,390	-	120,716	122,955	-	122,955	243,671
14	FEE 2 - 0.52%		2,988	2,600	223	186	186	186	186	-	2,988	3,568	-	3,568	6,556
15			962,012	641,981	71,025	66,842	66,842	66,842	66,842	-	962,012	980,374	-	980,374	1,942,386
16
17	Fully Loaded Values
18	Resource	Category	Prior Funding Received	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Prior Funding Received	FY23	FY24	Total This Action	New Work Total
19	Direct Labor	Labor	167,502	67,381	15,741	23,219	23,219	23,219	23,219	-	167,502	175,996	-	175,996	343,499
20	Contract Labor	Labor	369,493	572,000	16,840	40,941	40,941	40,941	40,941	-	369,493	752,606	-	752,606	1,122,098
21	Contract Materials	Material	287,964	-	32,293	-	-	-	-	-	287,964	32,293	-	32,293	320,257
22	Commoditiy #1	Commodities	-	-	-	-	-	-	-	-	-	-	-	-	-
23	Commoditiy #2	Commodities	33,345	-	-	-	-	-	-	-	33,345	-	-	-	33,345
24	Commoditiy #3	Commodities	100,719	-	-	-	-	-	-	-	100,719	-	-	-	100,719
25	Commoditiy #4	Commodities	-	-	2,496	2,496	2,496	2,496	2,496	-	-	12,480	-	12,480	12,480
26	Commoditiy #5	Commodities	-	-	3,432	-	-	-	-	-	-	3,432	-	3,432	3,432
27	Commoditiy #6	Commodities	-	-	-	-	-	-	-	-	-	-	-	-	-
28			959,023	639,381	70,802	66,656	66,656	66,656	66,656	-	959,023	976,807	-	976,807	1,935,830
29
30	Delta:		(2,988)	(2,600)	(223)	(186)	(186)	(186)	(186)
Sheet1

Cell Formulas
Range	Formula
K3:K15	K3	=+C3
L19:L28,L3:L15	L3	=SUM(D3:J3)
N3:N15	N3	=+M3+L3
O19:O28,O3:O15	O3	=SUM(K3:M3)
C12:J12,M12,C28:J28	C12	=SUM(C3:C11)
C13:J13	C13	=SUM(C3:C11)*14.4%
C14:J14	C14	=+IF(SUMIF($A$3:$A$11,"Contract*",$O$3:$O$11)>999999,SUMIF($A$3:A$11,"Contract*",C$3:C$11)*0.52%,0)
C15:J15	C15	=SUM(C12:C14)
M15	M15	=SUM(M13:M14)
C18:J18,M18:O18	C18	=+C2
K18:L18	K18	=K2
N19:N28	N19	=SUM(L19:M19)
C19:J27	C19	=IF(AND($A19="SACOM*",SUMIF($A$3:$A$11,"SACOM*",$O$3:$O$11)>999999),C3*0.52%+C3*1.144,C3*1.144)
K19:K28	K19	=SUM(C19)
C30:I30	C30	=+C28-C15

 

[Alex Blakenburg]

Try changing this:

$A20="Contract*"

To either of these:

LEFT($A20,8)="Contract"

or

COUNTIFS($A20,"Contract*")

 

[Candyman8019]

It also looks like C19:J27 are looking for "SACOM*" rather than "CONTRACT*"...and as such, isn't adding the .52%

 

[Mississippi Girl]

It also looks like C19:J27 are looking for "SACOM*" rather than "CONTRACT*"...and as such, isn't adding the .52%

In the real file, it's looking for SACOM; I made an edit for the upload and didn't capture that change. It still doesn't work though

 

[Mississippi Girl]

In the real file, it's looking for SACOM; I made an edit for the upload and didn't capture that change. It still doesn't work though

Mr Excel Help.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Base Values
2	Resource	Category	Prior Funding Received	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Prior Funding Received	FY23	FY24	Total This Action	New Work Total
3	Direct Labor	Labor	146,418	58,899	13,760	20,296	20,296	20,296	20,296		146,418	153,843	-	153,843	300,261
4	Contract Labor	Labor	322,983	500,000	14,720	35,788	35,788	35,788	35,788		322,983	657,872	-	657,872	980,855
5	Contract Materials	Material	251,717		28,228						251,717	28,228	-	28,228	279,945
6	Commoditiy #1	Commodities									-	-	-	-	-
7	Commoditiy #2	Commodities	29,148								29,148	-	-	-	29,148
8	Commoditiy #3	Commodities	88,041								88,041	-	-	-	88,041
9	Commoditiy #4	Commodities			2,182	2,182	2,182	2,182	2,182		-	10,909	-	10,909	10,909
10	Commoditiy #5	Commodities			3,000						-	3,000	-	3,000	3,000
11	Commoditiy #6	Commodities									-	-	-	-	-
12			838,307	558,899	61,890	58,266	58,266	58,266	58,266	-	838,307	853,852	-	853,852	1,692,159
13	FEE 1 - 14.4%		120,716	80,481	8,912	8,390	8,390	8,390	8,390	-	120,716	122,955	-	122,955	243,671
14	FEE 2 - 0.52%		2,988	2,600	223	186	186	186	186	-	2,988	3,568	-	3,568	6,556
15			962,012	641,981	71,025	66,842	66,842	66,842	66,842	-	962,012	980,374	-	980,374	1,942,386
16
17	Fully Loaded Values
18	Resource	Category	Prior Funding Received	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Prior Funding Received	FY23	FY24	Total This Action	New Work Total
19	Direct Labor	Labor	167,502	67,381	15,741	23,219	23,219	23,219	23,219	-	167,502	175,996	-	175,996	343,499
20	Contract Labor	Labor	369,493	572,000	16,840	40,941	40,941	40,941	40,941	-	369,493	752,606	-	752,606	1,122,098
21	Contract Materials	Material	287,964	-	32,293	-	-	-	-	-	287,964	32,293	-	32,293	320,257
22	Commoditiy #1	Commodities	-	-	-	-	-	-	-	-	-	-	-	-	-
23	Commoditiy #2	Commodities	33,345	-	-	-	-	-	-	-	33,345	-	-	-	33,345
24	Commoditiy #3	Commodities	100,719	-	-	-	-	-	-	-	100,719	-	-	-	100,719
25	Commoditiy #4	Commodities	-	-	2,496	2,496	2,496	2,496	2,496	-	-	12,480	-	12,480	12,480
26	Commoditiy #5	Commodities	-	-	3,432	-	-	-	-	-	-	3,432	-	3,432	3,432
27	Commoditiy #6	Commodities	-	-	-	-	-	-	-	-	-	-	-	-	-
28			959,023	639,381	70,802	66,656	66,656	66,656	66,656	-	959,023	976,807	-	976,807	1,935,830
29
30	Delta:		(2,988)	(2,600)	(223)	(186)	(186)	(186)	(186)
Sheet1

Cell Formulas
Range	Formula
K3:K15	K3	=+C3
L19:L28,L3:L15	L3	=SUM(D3:J3)
N3:N15	N3	=+M3+L3
O19:O28,O3:O15	O3	=SUM(K3:M3)
C12:J12,M12,C28:J28	C12	=SUM(C3:C11)
C13:J13	C13	=SUM(C3:C11)*14.4%
C14:J14	C14	=+IF(SUMIF($A$3:$A$11,"Contract*",$O$3:$O$11)>999999,SUMIF($A$3:A$11,"Contract*",C$3:C$11)*0.52%,0)
C15:J15	C15	=SUM(C12:C14)
M15	M15	=SUM(M13:M14)
C18:J18,M18:O18	C18	=+C2
K18:L18	K18	=K2
N19:N28	N19	=SUM(L19:M19)
C19:J27	C19	=IF(AND($A19="Contract*",SUMIF($A$3:$A$11,"Contract*",$O$3:$O$11)>999999),C3*0.52%+C3*1.144,C3*1.144)
K19:K28	K19	=SUM(C19)
C30:I30	C30	=+C28-C15

 

[Alex Blakenburg]

In the real file, it's looking for SACOM; I made an edit for the upload and didn't capture that change. It still doesn't work though

Did you try either of the suggestions in my Post #6 ?
Mind you for SACOM if you use left you would have to change the 8 to a 5.