SUMIFS challenge

[daveyc18]

so in column G...i have there are a bunch of codes, but in text format (e.g, 0410, 0400, 2000, etc.)

i want to sum column P where anything in column G is greater than 2000....the issue ofc is that column G is text so it doesnt seem to work when i try ">2000"

 

[myall_blues]

Try this. Note there is a single quote before the second double quote, and before the last double quote:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1															Sumifs:	18
2
3							0410									1
4							0400									2
5							1056									3
6							1560									4
7							2000									5
8							2100									6
Sheet1

Cell Formulas
Range	Formula
P1	P1	=SUMIFS(P3:P8,G3:G8,">'" & 1050 &"'")

 

[Dave Patton]

try =SUMIFS(P3:P8,G3:G8,">'0999'")

 

[Dave Patton]

edit to post 3. I did not read the question carefully enough.

T202411a.xlsm
	G									P	Q
1											6
2
3	0410									1
4	0400									2
5	1015									3
6	1560									4
7	2000									5
8	2100									6
9
2k

Cell Formulas
Range	Formula
Q1	Q1	=SUMIFS(P3:P8,G3:G8,">'2000'")

 

[arthurbr]

Perhaps

=SUMPRODUCT(--(VALUE(G3:G8)>2000)*(G3:G8))

 

[kvsrinivasamurthy]

tRY

=SUMPRODUCT(IFERROR(ISNUMBER($G$3:$G$8)*(VALUE($G$3:$G$8)>2000),0)*($P$3:$P$8))

 

[Dave Patton]

T202411a.xlsm
	G									P	Q
1											6
2											6
3	0410									1
4	0400									2
5	1015									3
6	1560									4
7	2000									5
8	2100									6
2k

Cell Formulas
Range	Formula
Q1	Q1	=SUMPRODUCT(--(--($G$3:$G$8)>2000),P3:P8)
Q2	Q2	=SUMIFS(P3:P8,G3:G8,">'2000")

 

[Dave Patton]

No information or feedback was posted.
With older versions of Excel, I would use Sumproduct.
With 365, I would use Sum.

Excel's Formulas | Formula Auditing | Formula Evaluate shows the logic.
The double negative coerces the text to a number and ">" coerces the True or False to 1 or 0. Multiplying by 0 yields 0 and multiplying by 1 yields the number.

T202411a.xlsm
	G									P	Q
1											41
2											41
3	0410									1	41
4	2000									5
5	2100									6
6	0410									7
7	0400									8
8	5500									17
9	9999									18
10
2k

Cell Formulas
Range	Formula
Q1	Q1	=SUMPRODUCT(--(--($G$3:$G$9)>2000),P3:P9)
Q2	Q2	=SUMIFS(P3:P9,G3:G9,">'2000")
Q3	Q3	=SUM((--G3:G9>2000)*P3:P9)