I Can't figure out this formula!!!
Posted by Lacy on February 01, 2002 11:11 AM
I am having a problem coming up with a formula in excel. Let's see...how do i explain this...
I have done a rough outline of what my spreadsheet looks like.
name ! Category# ! yes ! No !
-----------------------------
bob ! 7 ! x ! !
dan ! 4 ! ! x !
bill ! 7 ! ! x !
NOW IN this I acually have about 100 people, I need to come up with a formula that calculates how many #7's were marked with a yes. Pleas Help me out.
Posted by faster on February 01, 2002 11:56 AM
You might use a pivot table.
Make sure your data is in a table format.
No blank rows/columns, and has column headings.
Highlight your table. Select Data/Pivot Table.
This Pivot Table Wizard will appear.
Select Microsoft List
Click Next
make sure the range of your data is selected and
Click Next
Drag category to the Row area of the table (on the wizard)
And drag Yes/No to the Data area of the table
Make sure Yes/No quantifies and Count
Click Finish.
Posted by George on February 01, 2002 12:25 PM
Lacy
If the name is in A2, the catagory in B2, yes in C2 and no in D2 then put this formula in E2
=IF(AND(B2=7,C2=1),1,0) then sum the E column.
Copy this formula down as far as you need to.
NOTE: You will have to use a 1 (number one) in the C column. Excel won't see an X
Posted by Ivan F Moala on February 01, 2002 1:06 PM
You could Array enter this formula
=SUM((B2:B102=7)*(C2:C102<>""))
ie. enter formula using Ctrl + shft + enter
Assumes your range is B2:B102
See Mrexcel tip on CSE formulas
Ivan
