Solved

Formula help

Posted on 2011-09-26
7
152 Views
Last Modified: 2012-05-12
Hello,

Can someone please explain to me how I would get the following?

On the attached spreadsheet I have some data that I would like to add together.
I would like to count for example both column A and column H as one if they both say “Yes”
Would this be an IF statement?
 example.xlsx
0
Comment
Question by:vegas86
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 36640608
Hello,

if you want to return a 1 if both column A and H in a given row equals Yes, then use in row 2

=IF(AND(A2="yes",H2="yes"),1,0)

copy down.

cheers, teylyn
0
 

Author Comment

by:vegas86
ID: 36644130
Hey Teylyn,

Sorry I worded that wrong. What you did is perfect but is there any chance i can do the entire columns at once? so count how many rows that have yes is both columns A and H and return a number?
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36645596
... or, if you're trying to count Yes's but treat A and H as one then...

=COUNTIF(A2:M2,"Yes")-AND(A2="Yes",H2="Yes")

Please see attached.

Regards,
Brian.
example-V3.xlsx
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 51

Expert Comment

by:HainKurt
ID: 36647579
N1: BothYes
N2: =IF(AND(A2="Yes", H2="Yes"),1,0)
Copy down

O1:Total
O2:==SUM(N:N)

you can hide column N if you wish
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36647762
here how it works...
example.xlsx
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 500 total points
ID: 36648356
You can use countifs()

=COUNTIFS(A:A,"yes",H:H,"yes")

cheers, teylyn
0
 

Author Closing Comment

by:vegas86
ID: 36669249
Thanks for all your help and suggestions guys. Thank you Teylyn the countif was exactly what i needed!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now