Excel - Add figures dependending on the results of other cells-CodePudding

It may be that I have tried to use the correct formula, but have misused it but so far I haven't found anything that seems to be working for me.

I have 6 individual conditions, that when each is met there is a % attributed to that item that I want to be returned. At times none of these conditions may be met, at other times all may be met, so I will get anywhere from 0% to 100% as possible return values. Everytime I want each of the true values to be added together to give me the sum of these, and the false values to give me nothing.

It is essentially that if there is income for each item, there is a known % of the total budgeted service costs that will then be required, and to be able to best estimate the costs associated with each level of income as it may change until at 100% I want to be able to put this in to a formula.

So on the below for example in Jan, the % to be returned should be 89%, as only Item 5 did not see any income

        Month   Jan Feb Mar
%       Item    Income  Income  Income
13%     1   400 134 397
20%     2   411 -   14
21%     3   200 127 231
23%     4   84  127 -
11%     5   -   427 218
11%     6   4   5   320

I have attempted to use multiple ways with it ending in sumifs and countifs, maybe I was putting them together wrong but I wasn't able to get the desired result. The - it was also not picking up as a 0 value, the format is currency, but to be check if there is income I am simply going with if the income foe this item, in this month is >0 then return the %

CodePudding user response：

Jack,

Welcome to SO!

The formula you want is: =SUMIF(C3:C8,">0",$A$3:$A$8)

CodePudding user response：

If you just enter a minus sign, it will be treated as a single-character string rather than a number, so if you want to test for inequality or equality you have to compare it against a string:

=SUMIF(C3:C8,"<>-",$A3:$A8)

=1-SUMIF(C3:C8,"-",$A3:$A8)

(I suspect that there may be additional decimal places that we can't see to explain the discrepancy between the results with these two formulas).