Multiple Variables in an Excel Formula & SUMIF

[Guest]

Here's my problem(s)

I have 5 columns. Column A is information I enter. (The information will be numbers 1-8). In column B, I want a formula that will examine information entered in column A and based upon that number, will display one of 8 different variables col A: col B
"1" "15500
"2" "17700
"3" "19950" & so forth.

Column C is information I enter. In Column D, I have a formula: =IF(B9<=C9, "50% or below","OVER 50%"). My next problem: I need a formula that will compute the sum of all cells that return the value "50% or below". I want each "50% or below" result to equal 1. I have tried SUMIF(D926, "50% or below",A9:A26") but that will only work if all answers in Column A were "1"

I hope I made my questions clear. Please advise me on suggestions or questions

 

[Paul]

Here's my problem(s):

I have 5 columns. Column A is information I enter. (The information will

be numbers 1-8). In column B, I want a formula that will examine information
entered in column A and based upon that number, will display one of 8
different variables col A: col B:

"1" "15500"
"2" "17700"
"3" "19950" & so forth.

Column C is information I enter. In Column D, I have a formula:

=IF(B9<=C9, "50% or below","OVER 50%"). My next problem: I need a formula
that will compute the sum of all cells that return the value "50% or below".
I want each "50% or below" result to equal 1. I have tried SUMIF(D926,
"50% or below",A9:A26") but that will only work if all answers in Column A
were "1".

I hope I made my questions clear. Please advise me on suggestions or questions.

For column B, you could use the CHOOSE function with the 8 values listed
within it, or a VLOOKUP function with the list of 8 defined elsewhere in the
workbook.

For column D, you don't want to sum anything - you want to count:
=COUNTIF(D926,"50% or below")

 

[Don Guillett]

1. Look in HELP index for CHOOSE or LOOKUP
2 =SUMIF(G1:G5,"<="&0.5,H1:H5)
or =sumproduct((rngA<=.50)*rngB)

--
Don Guillett
SalesAid Software
(e-mail address removed)

Here's my problem(s):

I have 5 columns. Column A is information I enter. (The information will

be numbers 1-8). In column B, I want a formula that will examine information
entered in column A and based upon that number, will display one of 8
different variables col A: col B:

"1" "15500"
"2" "17700"
"3" "19950" & so forth.

Column C is information I enter. In Column D, I have a formula:

=IF(B9<=C9, "50% or below","OVER 50%"). My next problem: I need a formula
that will compute the sum of all cells that return the value "50% or below".
I want each "50% or below" result to equal 1. I have tried SUMIF(D926,
"50% or below",A9:A26") but that will only work if all answers in Column A
were "1".