Summing on multiple conditions
 

Hey everyone!

Here's what I've got:

1) COUNTRIES
2) PRIORITY
3) COMPONENT (Y/N)
4) TOTAL QUESTIONS - This is a column containing numeric values based on the
survey the end user received
5) QUESTIONS ANSWERED
7) PROGRAMS

I need to know the following:

Count of PROGRAMS for each COUNTRY with a specific PRIORITY and COMPONENT *
Sum of TOTAL QUESTIONS meeting the Count of criteria.

Number of QUESTIONS ANSWERED where the count of PROGRAMS for each COUNTRY
with a specific PRIORITY and COMPONENT

In a nutshell, I have 16 different countries I'm trying to collect data on.
I have all my raw data in a tab other than where my formulas are. I know
(for example) there are 10 programs for 'India' that are 'priority 2'
'infrastructure'. I have a column that is labeled 'total number of
questions' - this is the number of questions on the survey given. I know
there are 50 questions on this survery, so 50 questions*10 programs= 500 -
but, how do i get this automatically without having to go figure it out
manually?

The second item is the same things as above (10 programs for india that are
priority 2 infrastructure. 50 questions on the survey.) but, I also need to
know out of the 'total number of questions' for all 'priority 2'
'infrastructures', how many questions have been answered to date. India has
answered 250 of the questions out of 500. But, this is across 10 programs.

Anyone have any advise here?

sounds like a good problem for sumproduct

=sumProduct(--(countries="india"),--(Priority=2),--(component="infrastructure"),total questions)

will give the number of questions asked to india with priority 2
infrastructure
=sumProduct(--(countries="india"),--(Priority=2),--(component="infrastructure"),questions answered)

will give the number of questions answered for the same criteria.

by playing with the differert sections I think you will be able to answer
your questions.

the --( makes the lofgical true false become a 1, 0 numeric.

the arrays in each section must be the same size but the shorthand for
complete columns(A:A) won't work

"Stacy" wrote:

Hey everyone!

Here's what I've got:

1) COUNTRIES
2) PRIORITY
3) COMPONENT (Y/N)
4) TOTAL QUESTIONS - This is a column containing numeric values based on the
survey the end user received
5) QUESTIONS ANSWERED
7) PROGRAMS

I need to know the following:

Count of PROGRAMS for each COUNTRY with a specific PRIORITY and COMPONENT *
Sum of TOTAL QUESTIONS meeting the Count of criteria.

Number of QUESTIONS ANSWERED where the count of PROGRAMS for each COUNTRY
with a specific PRIORITY and COMPONENT

In a nutshell, I have 16 different countries I'm trying to collect data on.
I have all my raw data in a tab other than where my formulas are. I know
(for example) there are 10 programs for 'India' that are 'priority 2'
'infrastructure'. I have a column that is labeled 'total number of
questions' - this is the number of questions on the survey given. I know
there are 50 questions on this survery, so 50 questions*10 programs= 500 -
but, how do i get this automatically without having to go figure it out
manually?

The second item is the same things as above (10 programs for india that are
priority 2 infrastructure. 50 questions on the survey.) but, I also need to
know out of the 'total number of questions' for all 'priority 2'
'infrastructures', how many questions have been answered to date. India has
answered 250 of the questions out of 500. But, this is across 10 programs.

Anyone have any advise here?