Need formula help

[easybpw]

Hi everyone,

Thanks in advance for the help. I created a small example of a work issue we are having. Hopefully someone can help. I need to get the total of column C if column A has 1111 at the left (will always be 4 characters) and if column B is greater than 0. Struggling to figure this out.

 

[duggie33]

Hi easybpw,

I think there is some missing information. "Total of column C." A sum of all the values? A sum of those with "1111" in column A?

In any case, maybe the following will get you headed the right direction.

Book_2023-09-10.xlsm
	A	B	C	D
1	Test1	Test2	test2	Results
2	1111-2456	569	2232	2232
3	1111-2345	85	1000	1000
4	1598-8520	0	258
5	1111-2222	0	555
Sheet9

Cell Formulas
Range	Formula
D2:D5	D2	=IF(AND(LEFT(A2,4)="1111",B2>0),C2,"")

Hope that helps,

Doug

 

[JamesJones25]

=SUMIFS(C:C, A:A, "1111*", B:B, ">"&0)

 

[dreid1011]

Perhaps this:

=SUMPRODUCT(--(LEFT($A$2:$A$4,4)="1111"),--($B$2:$B$4>0),$C$2:$C$4)

And you can change the "1111" to be a cell reference if you need to change the beginning numbers of column A.