Gday. I have a issue that has me pulling out my hair. I have to report a value based on the following criteria : Its date raised, what type of valuation it is and it must be a reportable case. I have the following names " Date_raised, Value, Type_of_valuation (there are multiple types that are recorded on another sheet) and Reportable (Yes/No option). Presently I have the following "Sum(if(Date_raised_=A27,Value)*(Type_of_Valuation='Sheet1'!A2))} Works great - problem is that it does not take into account if it is reportable or not. please help
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Sumif Dependant Multiple Named Criteria Problem
- Thread starter KC12345
- Start date
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
=SUMIFS(Value,Date_raised,A27,Type_of_Valuation,Sheet1!A2,Reportable,"Yes")
should do what you want.
Upvote
0
Nope - #value error
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
What do you get with the following?
1.
=SUMIFS(Value,Date_raised,A27,Type_of_Valuation,Sheet1!A2)
2.
=SUMIFS(Value,Date_raised,A27)
3.
=SUM(Value)
Upvote
0
I get the total value for that date which is ok - but, there is still the issue of reportable and not reportable. So I have multiple Yes or No's on the specific date raised - the formulas above are taking into account every one
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
Those 1 to 3 were meant as diagnostics.
What do you really have in the Reportable range: Yes, TRUE, or 1?
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
Why don't you answer as I pleaded?
What do you get with the following?
1.
=SUMIFS(Value,Date_raised,A27,Type_of_Valuation,Sheet1!A2)
2.
=SUMIFS(Value,Date_raised,A27)
3.
=SUM(Value)
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
In that case, getting a #VALUE! error with:
=SUMIFS(Value,Date_raised,A27,Type_of_Valuation,Sheet1!A2,Reportable,"Yes")
could mean that:
Reportable is not of the same size as the other named ranges. You can check this with:
=ROWS(Values)=ROWS(Reportable)
Upvote
0
