Solved

Formula help

Posted on 2011-09-26
7
155 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

840 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