Sumif Dependant Multiple Named Criteria Problem

[KC12345]

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

 

[Aladin Akyurek]

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

=SUMIFS(Value,Date_raised,A27,Type_of_Valuation,Sheet1!A2,Reportable,"Yes")

should do what you want.

 

[KC12345]

=SUMIFS(Value,Date_raised,A27,Type_of_Valuation,Sheet1!A2,Reportable,"Yes")

should do what you want.

Nope - #value error

 

[Aladin Akyurek]

Nope - #value error

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)

 

[KC12345]

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)

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

 

[Aladin Akyurek]

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

Those 1 to 3 were meant as diagnostics.

What do you really have in the Reportable range: Yes, TRUE, or 1?

 

[KC12345]

Sure - I get that - and they all work. "Reportable" range is a data validated Yes or No result only (does have blanks further in the sheet for non entered data)

 

[Aladin Akyurek]

Sure - I get that - and they all work. "Reportable" range is a data validated Yes or No result only (does have blanks further in the sheet for non entered data)

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)

 

[KC12345]

I don't follow sorry ? All those formulas provided results with no errors.

 

[Aladin Akyurek]

I don't follow sorry ? All those formulas provided results with no errors.

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)