# Sumif Dependant Multiple Named Criteria Problem

#### KC12345

##### New Member
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

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.

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

should do what you want.

Nope - #value error

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)

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

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?

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)

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)

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 don't follow sorry ? All those formulas provided results with no errors.

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)

Replies
3
Views
165
Replies
7
Views
147
Replies
8
Views
664
Replies
4
Views
484
Replies
3
Views
352

1,219,519
Messages
6,148,749
Members
450,833
Latest member
Andyboi

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back