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.	​	​	​

 

[Skyybot]

Where are you placing this formula?

 

[awoohaw]

how about: (this gives sum for each row that meets criteria, next message give aggregate)

Mr Excel Questions 9.xlsm
	A	B	C	D	E	F	G
1		A	B	C	D	E	2022
2	1	3	0	0	2020-07-02	64XBC10	0
3	2	2	0	0	2020-07-02	64XBC10	0
4	3	6	0	0	2020-07-02	64XBC10	0
5	4	16	4	0	2022-07-02	64FX12	20
6	5	3	0	2	2022-07-02	64XA12	5
7	6	1	4	0	2022-07-02	64FGT12	0
8	7	1	0	0	2021-07-02	64FX12	0
9	8	2	0	0	2021-07-02	64XFC15	0
10	9	3	1	1	2021-07-02	64XFC15	0
11	10	1	0	0	2021-07-02	64XFC15	0
Sheet5

Cell Formulas
Range	Formula
G2:G11	G2	=(--(G1=YEAR(E2:E11)))* (ISNUMBER(FIND("X",F2:F11)))*(B2:B11+C2:C11+D2:D11)
Dynamic array formulas.

 

[awoohaw]

=SUM((--(G1=YEAR(E2:E11)))*(ISNUMBER(FIND("X",F2:F11)))*(B2:B11+C2:C11+D2:D11))

 

[Tikey]

Where are you placing this formula?

Hi

I have put this formula just in another cell where I want the answer

 

[etaf]

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)))))

 

[JoeMo]

Another way:

Book1
	A	B	C	D	E	F	G
1	3			9/7/2020	64XBC10		25
2	2			9/7/2020	64XBC10
3	6			9/7/2020	64XBC10
4	16	4		9/7/2022	64FX12
5	3		2	9/7/2022	64XA12
6	1	4		10/7/2022	64FGT12
7	1			11/7/2021	64FX12
8	2			7/13/2021	64XFC15
9	3	1	1	7/13/2021	64XFC15
10	1			7/16/2021	64XFC15
Sheet1

Cell Formulas
Range	Formula
G1	G1	=SUM(IF((YEAR(D1:D10)=2022)*(ISNUMBER(SEARCH("x",E1:E10))),A1:C10,0))

 

[Tikey]

=SUM((--(G1=YEAR(E2:E11)))*(ISNUMBER(FIND("X",F2:F11)))*(B2:B11+C2:C11+D2:D11))

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

 

[awoohaw]

I don't think so. You would be multiplying some items by zero making the value zero. But you can try.

 

[Tikey]

I don't think so. You would be multiplying some items by zero making the value zero. But you can try.

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