please help

  • Thread starter Thread starter T.N.
  • Start date Start date
T

T.N.

Hello: I am trying to create a formula in excel. The idea
is simple. I have a numerical value in A1 and I want to
create a formula in B1 (if statement). The statment is,
if in the range of A2 through IV1000 (Just any large
boxed area starting at A2) you find a 1, take the number
of occurrences of 1 in any of the cells in the specified
boxed range and multiply it by B2. So if in that area it
finds 12 1's, I want the value is B1 to be A1(12) That is
the value I want. How can I do that?
 
To get the number of 1's try
=COUNTIF(A2:IV1000,1)

I'm not sure I follow what you want to do with that value,
but any other operation just add to the formula.

If you want to multiply by A1 Use
=COUNTIF(A2:IV1000,1)*A1

If the number you want to count is in A1 use:
=COUNTIF(A2:IV1000,A1)

A more clear explanation would help
JS
 
Clarification:

I have a system where I have values In A2 through D2.
Underneath A2 through D2 is a four column by 20 row box.

The value is A1 is the formula of this. If you find a 1
in the box, refer to its corresponding value in row 2.
Add up all the occurrences of 1, but not with 1, instead
with it's corresponding value in row 2. Is that clear? I
hope so.

A1
$100 $200 $300 $400
1
1
1
1

So A1=$100+$100+$200+$300

I am going to do this same thing for B1 and 2, and C1 and
3, and D1 and 4 and so on. But I figure once I get the
formula for A1 I'll be fine. I'm still having problems
with the COUNTIF function.

THANK YOU!!!!!
 
Clarification:

I have a system where I have values In A2 through D2.
Underneath A2 through D2 is a four column by 20 row box.

The value is A1 is the formula of this. If you find a 1
in the box, refer to its corresponding value in row 2.
Add up all the occurrences of 1, but not with 1, instead
with it's corresponding value in row 2. Is that clear? I
hope so.

A1
$100 $200 $300 $400
1
1
1
1

So A1=$100+$100+$200+$300

I am going to do this same thing for B1 and 2, and C1 and
3, and D1 and 4 and so on. But I figure once I get the
formula for A1 I'll be fine. I'm still having problems
with the COUNTIF function.

THANK YOU!!!!!
 
in your example you can use the following

=(COUNTIF(A3:A24,1)*A2)+(COUNTIF(B2:B24,1)*B2)+(COUNTIF(C2:C24,1)*C2)+(COUNTIF(D2:D24,1)*D2)

Just change the range to include all the data in each column

Randall
 
Back
Top