Sumproduct / Sumifs

[Tikey]

Hi

I am trying to sum a number of values in 3 columns, if the year from a date in another column is correct and if the values in another column contain an x, which could be in any position in the cells. My spreadsheet could contain several thousand rows, but I have included a small example. I am also wishing to avoid using a helper column.

A​ B​ C​ D​ E​ 1​ 3​ 09/07/2020​ 64XBC10 2​ 2​ 09/07/2020​ 64XBC10 3​ 6​ 09/07/2020​ 64XBC10 4​ 16​ 4​ 09/07/2022​ 64FX12 5​ 3​ 2​ 09/07/2022​ 64XA12 6​ 1​ 4​ 10/07/2022​ 64FGT12 7​ 1​ 11/07/2021​ 64FX12 8​ 2​ 13/07/2021​ 64XFC15 9​ 3​ 1​ 1​ 13/07/2021​ 64XFC15 10​ 1​ 16/07/2021​ 64XFC15 So, if I wanted to sum the values in columns A,B,C if the year was 2022 and the values in column E contained an x, I would expect the result to be 25. I have managed to do this with SUMIFS using the following formula, but it will only sum a single column, if I try to sum all 3 I get a #Value! error.	​		​
​	​		​
=SUMIFS(A2:A11,D2:D11,">="&DATE(2022,1,1),$D2:$D11,"<="&DATE(2022,12,31),E2:E11,"*x*")			​
​	​	​	​
I have tried to use SUMPRODUCT without success, but believe you cannot use wildcards with this. Can anyone offer a solution please.	​	​	​

 

[awoohaw]

Thanks very much, that works great. Rather than using refs for the 3 seperate columns, could I just use b2:d11 ?

I best not in case I get a incorrect answer and I don't realise. Thanks very much for your help.

also, i placed a reference for the year (if you don't want that you can hard code it in the formula). Additionally, I used FIND with an upper case X, the search function doesn't care about upper or lower case.

if you find that one of the posts here solves your questions. please mark that post as the answer.

Best wishes! Happy to help.

 

[Tikey]

how about
=SUM(FILTER($A$2:C$33,(YEAR($D$2:$D$33)=2022)*(ISNUMBER(SEARCH("X",$E$2:$E$33,1)))))

Book7
	A	B	C	D	E	F	G
1
2	3			7/9/20	64XBC10		25
3	2			7/9/20	64XBC10
4	6			7/9/20	64XBC10
5	16	4		7/10/22	64FX12
6	3		2	7/11/22	aaaX
7	1	4		7/12/22	64FGT12
8	1			1/1/23	64FX12
9	2			1/2/23	64XFC15
10	3	1	1	1/3/23	64XFC15
11	1			1/4/23	64XFC15
Sheet1

Cell Formulas
Range	Formula
G2	G2	=SUM(FILTER($A$2:C$32,(YEAR($D$2:$D$32)=2022)*(ISNUMBER(SEARCH("X",$E$2:$E$32,1)))))

That works well, thank you.

 

[etaf]

you are welcome