Solved

How to add an additional complex countif to an established sumproduct/countif?

Posted on 2013-06-12
2
224 Views
Last Modified: 2013-06-12
I have a file attached that is currently counting the various codes in column D of the Summaries tab based on the descriptions in column B of the Schedule Codes tab.

I would like to have an initial screening based on the managment areas in column C of the Summaries tab. I copied all of the info I had for area 1A two times. That is why the current formulas show 3 times what the answer should be. I would like each Summary box on the Summaries tab to only show the information if the area in column C matches what is in row 2 of the summary boxes.

What should I add to my current formulas to do this?

Thanks!
ExpertHelp.xlsx
0
Comment
Question by:Dominator1025
2 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
Comment Utility
You can change the COUNTIF part of the formula to a COUNTIFS to check the extra criterion, e.g. with this formula in I3

=SUMPRODUCT(--ISNUMBER(SEARCH("residential",'Schedule Codes'!$B$1:$B$1000))*COUNTIFS($C$1:$C$973,I2,$D$1:$D$973,'Schedule Codes'!$A$1:$A$1000))

....and similar for your other calculations

regards, barry
0
 

Author Closing Comment

by:Dominator1025
Comment Utility
Barry,

Awesome as always, thanks!

Dom
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

772 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

11 Experts available now in Live!

Get 1:1 Help Now