Link to home
Start Free TrialLog in
Avatar of avgplusguy
avgplusguyFlag for United States of America

asked on

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
Avatar of byundt
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of avgplusguy


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