Adding multiple columns with criteria in one column

[rgillson3]

I'm trying to "pull" some data from an Aged A/P report.

I have numbers/amounts in Columns I thru O.

In Column R I have some rows with text like "EFT 3/29-Ent". These all indicate dates for a particular location.

I'd like to add any amount shown in Columns I thru O if its row contains "Ent" in Column R.

I've tried =SUM((I8:M108)*(--(R8:R108="*Ent*"))) and =SUMPRODUCT(I8:P108*--(R8:R108="*Ent*")) and only get #VALUE! for either.

What am I missing?

 

[SanjayGMusafir]

I believe there has to be some better way using FILTER Function. Till then try this and revert -

=SUMIFS(I8:I108,R8:R108,"*Ent*")+SUMIFS(J8:J108,R8:R108,"*Ent*")+SUMIFS(K8:K108,R8:R108,"*Ent*")+SUMIFS(L8:L108,R8:R108,"*Ent*")+SUMIFS(M8:M108,R8:R108,"*Ent*")+SUMIFS(N8:N108,R8:R108,"*Ent*")+SUMIFS(O8:O108,R8:R108,"*Ent*")

 

[rgillson3]

I believe there has to be some better way using FILTER Function. Till then try this and revert -

=SUMIFS(I8:I108,R8:R108,"*Ent*")+SUMIFS(J8:J108,R8:R108,"*Ent*")+SUMIFS(K8:K108,R8:R108,"*Ent*")+SUMIFS(L8:L108,R8:R108,"*Ent*")+SUMIFS(M8:M108,R8:R108,"*Ent*")+SUMIFS(N8:N108,R8:R108,"*Ent*")+SUMIFS(O8:O108,R8:R108,"*Ent*")

That seems to work. I was combining multiple "sumif" formulas together to get the same result.

I was hoping for a shorter formula.

I stumble upon the FILTER function as well. That seems to return the individual values though. I only want the total.

 

[SanjayGMusafir]

Not sure but I think Filter can be put inside SUM function

 

[rgillson3]

That is beyond my current capability or knowledge.

 

[SanjayGMusafir]

I told you we can pass Filtered results through SUM function

Check this and revert -

=SUM(TOCOL(FILTER(I8:O108,ISNUMBER(SEARCH("Ent",R8:R108))),,True))

I could only check it until Filter function as below. Since I don't have Excel 365, I couldn't test further.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2	1								EFT 3/29-Ent		1	0	0	0	0	0
3		1							EFT 3/29-Ent		0	1	0	0	0	0
4			1						EFT 3/29-Ent		0	0	1	0	0	0
5				1					EFT 3/29-Ent		0	0	0	1	0	0
6					1				EFT 3/29-Ent		0	0	0	0	1	0
7						1			EFT 3/29-Ent		0	0	0	0	0	1
Sheet1

Cell Formulas
Range	Formula
K2:P7	K2	=FILTER(A:F,ISNUMBER(SEARCH("Ent",I:I)))
Dynamic array formulas.

But I know it would work, as I derived Idea from the video below -

 

[rgillson3]

That worked! Thank you.

 

[SanjayGMusafir]

Glad to help you and thanks for the feedback.