Hello friendly people,
I've racked my brain on a formula with no luck. I've read a few other posts focused on COUNTIF and tried to change my results to 1 & 0 by using "--". Still, I get a #VALUE or #N/A error.
I would like to make a sum of data in a large array based on multiple criteria. I have 3 columns that make up my criteria, then a row that makes up another set of criteria and then the array of data to evaluate based on the results of my column and row checks (should result in 1 or 0).
<tbody>
</tbody>
I've tried two iterations, where Columns R & S I've listed the test criteria in cells.
1. =SUMPRODUCT(--('Balance Sheet_Property SS'!$D$13:$D$269=$R$18)*--('Balance Sheet_Property SS'!$C$13:$C$269=$R$19)*--('Balance Sheet_Property SS'!$F$13:$F$269=$R$20)*(('Balance Sheet_Property SS'!$IR$12:$MH$12=$S$18)*1+('Balance Sheet_Property SS'!$IR$12:$MH$12=$S$19)*1+('Balance Sheet_Property SS'!$IR$12:$MH$12=$S$20)*1),('Balance Sheet_Property SS'!$IR$13:$MH$269))
This produces a #VALUE result.
2. =SUMPRODUCT(--('Balance Sheet_Property SS'!$D$13:$D$269=$R$18)*--('Balance Sheet_Property SS'!$C$13:$C$269=$R$19)*--('Balance Sheet_Property SS'!$F$13:$F$269=$R$20)*--('Balance Sheet_Property SS'!$IR$12:$MH$12=$S$18:$S$20),('Balance Sheet_Property SS'!$IR$13:$MH$269))
This produces an #N/A result.
Any help appreciated. Thanks.
I've racked my brain on a formula with no luck. I've read a few other posts focused on COUNTIF and tried to change my results to 1 & 0 by using "--". Still, I get a #VALUE or #N/A error.
I would like to make a sum of data in a large array based on multiple criteria. I have 3 columns that make up my criteria, then a row that makes up another set of criteria and then the array of data to evaluate based on the results of my column and row checks (should result in 1 or 0).
Row 12 (Header Names) | Column C | Column D | Column F | Columns IR: Column MH | Columns IR: Column MH |
Criteria 1 (Text) | Criteria 2 (Text) | Criteria 3 (Text) | Criteria 4 (Text) | Sum array (Rows 13:269) |
<tbody>
</tbody>
I've tried two iterations, where Columns R & S I've listed the test criteria in cells.
1. =SUMPRODUCT(--('Balance Sheet_Property SS'!$D$13:$D$269=$R$18)*--('Balance Sheet_Property SS'!$C$13:$C$269=$R$19)*--('Balance Sheet_Property SS'!$F$13:$F$269=$R$20)*(('Balance Sheet_Property SS'!$IR$12:$MH$12=$S$18)*1+('Balance Sheet_Property SS'!$IR$12:$MH$12=$S$19)*1+('Balance Sheet_Property SS'!$IR$12:$MH$12=$S$20)*1),('Balance Sheet_Property SS'!$IR$13:$MH$269))
This produces a #VALUE result.
2. =SUMPRODUCT(--('Balance Sheet_Property SS'!$D$13:$D$269=$R$18)*--('Balance Sheet_Property SS'!$C$13:$C$269=$R$19)*--('Balance Sheet_Property SS'!$F$13:$F$269=$R$20)*--('Balance Sheet_Property SS'!$IR$12:$MH$12=$S$18:$S$20),('Balance Sheet_Property SS'!$IR$13:$MH$269))
This produces an #N/A result.
Any help appreciated. Thanks.