Formula for Multiple "IF's" or maybe "COUNTIF's"???

[MM Lines]

I have a worksheet that has various cells of data...some cells contain
numbers. Those numbers span negative (0.5) through (30.0) including half
numbers (21.5). Two cells over from those cells with these numbers is either
a YES or a NO. I want to count how many YES's and NO's there are for a
particular number. In other words a formula will be entered in a random cell
asking for the doc to be searched for all (19.5), when found, each case will
be analyzed by the second cell over for a YES or NO. The quantity of YES's
will be shown as a total in one cell and the quantity of NO's will be shown
in a cell below.

 

[carlo]

Hey MM Lines

In my example you enter the number you look for in D1,
The Range of the numbers is A1:A20 and the range for yes/no is in
B1:B20
i put the following formula for "YES":
=SUMPRODUCT(((A1:A20)=D1)*((B1:B20)="YES"))
the formula for "NO" is the same, just with a "NO" in the end.

hth
Carlo

 

[MartinW]

Hi MM,

You don't mention what your ranges are so I have just used
two at random, adjust to suit your actual data.
=SUMPRODUCT(--(H1:H20=19.5)*(J1:J20="yes"))

HTH
Martin

 

[Dave Peterson]

For YES's:

=sumproduct(--(a1:a100=19.5),--(c1:c100="Yes"))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

If you're using xl2007, you can look at =countifs() in Excel's help.

 

[MM Lines]

Carlo, thanks for the help...I'm almost there. Your explanation works if the
analsis is for only two columns but mine has multiple columns throughout a
worksheet with the target number shown in D1 of your example. Second problem
is throughout the worksheet where that D1 target number is found, the yes/no
is two cells to the right, or second column over.

 

[carlo]

Hi MM Lines

I am not quite sure, if i understand you exactly, but this works for
me:

=SUMPRODUCT(((A1:F30)=M1)*((OFFSET(A1:F30,0,2))="YES"))

Your Target cell should not be in the Range you search, that's why i
new changed it to M1.

Hope that makes sense, otherwise just ask

Carlo

 

[MM Lines]

Thanks again Carlos...that was perfect. I'm curious though. I understand
the formula perfectly except for the "0" in the OFFSET portion. What is that
zero signifying? I assume the "2" is becuase I need to have the cell two
over from my target analyzed.

As a caveat to this whole thing, I have the formula now entered in for about
60 rows with $'s in front of the cell labels. Two copy and paste this
easily, is there a way to just change portions of formula in mass quantities?
In other words if I want to change your M1 to M3 the next two rows and then
M5 for the following two rows is there some kind of paste special for just
that part of the formula?

 

[David Biddulph]

The syntax for OFFSET, as well as for all other Excel functions (except
DATEDIF) is available in Excel help.

If you want to change the M1 reference two rows at a time, I would suggest
you use OFFSET again, perhaps something along the lines of
OFFSET(M$1,INT((ROW()-ROW(M$1))/2),0)