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