Solved

Adding only records that meet a criteria

Posted on 2011-09-21
4
171 Views
Last Modified: 2012-05-12
Hello:

I have a spreadsheet with several columns in it.  The values that I want to add are in the columns.  I only want to add the columns where the heading for that column is "add".

Of course I can just choose those cells, but I am looking for a formula that will only add the values when the heading(or cell above them say "add")

I am attaching a small example.

Thank you! exampleofadd.xlsx
0
Comment
Question by:MeowserM
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 50

Expert Comment

by:barry houdini
ID: 36574254
Try using SUMIF function, i.e. this formula

=SUMIF(B$2:J$2,"add",B3:J3)

regards, barry
0
 

Author Comment

by:MeowserM
ID: 36574448
awesome - can I use this with countif too?
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 36574603
To count what? You can count the number of "add"s in a range, e.g.

=COUNTIF(B$2:J$2,"add")

....but if you want to count how many numbers in your range are in the "add" columns but also meet another criterion, e.g. larger than 5 then you can use COUNTIFS (with an "S") like this

=COUNTIFS(B$2:J$2,"add",B3:J3,">5")

regards, barry


0
 

Author Comment

by:MeowserM
ID: 36575001
That's perfect - Thank you Barry!!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
converting excel into labels 6 37
sort time order 10 45
just curious to lean why these two UDFs behaviors are different? 29 56
Office 365 Spam 3 31
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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…

732 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