# Excel Sumif #Value May need Sumproduct

Excel 365 Win10
=SUMIFS(Act!\$D\$1:\$AT\$23, \$D\$1:\$D\$23,"=Product A*",Act!Q\$1:\$\$AB!,"<=\$b\$8",Act!Q\$1:\$\$AB!,">=datevalue(year(\$b\$8),1,1))TestLine.xlsx
I want to sum all values for Product A and its variations YTD
\$B\$8 is cell where user puts the current month m/d/yyyy
Q1:AB1 standard first of month data 1/1/2021, 2/1/2021 etc
byundt

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.

That is the vast majority of the solution. You solved the problem I hinted at. Act column GREAT
I though Len(\$d14))=\$D14 would fail for two similar lines but different lengths, but it adds up the two lines that start with the same characters

The Bud tab is simpler and only has one one per product and is only 17 lines instead of 23
I change AB23 to AB17 and the total comes up close to 0.0065 so not zero but very close to zero
I thought maybe row two being blank was a problem, but i put 1 in front and back and the numbers did not change
I am thinking it should be simpler. However when i remove the Left logic it shows #Value
You had a wildcard character in your SUMIFS formula. SUMPRODUCT doesn't support wildcard characters, but the way you were using it could be mimicked using LEFT(Act!\$D\$1:\$D\$23,LEN(\$D14)).
Few things I've spotted:
SUMIFS only handles one column, you are trying to SUM D to AT
"=Product A*" should be "Product A*", don't need the = sign
The date criteria are wrong in few places:
1) Act!Q\$1:\$\$AB!,"<=\$b\$8"  should be Act!\$Q\$1:\$AB1,"<="&\$B\$8"  correction of \$'s, the ! instead of 1 after \$AB; the \$B\$8 reference will then capitalise automatically.
2) ">=datevalue(year(\$b\$8),1,1)"  should be ">="&DATE(YEAR(\$B\$8),1,1)