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
=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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
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)
Just looking at your header formula:
="Current Month as of "&B6
You have date in B8 and then using B6 to formulate the date into full month and year format. That can be done much simpler than what you've done, in C3 put:
="Current Month as of "&TEXT(B8,"mmmm yyyy")
Then don't need the B6 formula
="Current Month as of "&B6
You have date in B8 and then using B6 to formulate the date into full month and year format. That can be done much simpler than what you've done, in C3 put:
="Current Month as of "&TEXT(B8,"mmmm yyyy")
Then don't need the B6 formula
ASKER
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