I've created the beginnings of an Excel database to try to make our
end-of-year APR easier. We run a housing assistace program among other
things, and I need a little help, and, finding help here before, I figured I
would try again.

With our APR, we have to keep track of demographic data conditional on
whether the customer has received housing assistance. I could just create two
database spreadsheets for Rental Assistance and Supportive Services. I would
rather designate the one database to create two different demographic counts.
One for rental assistance, and one for those who did not receive rental
assistance.

For example:
Age
i. Under 18 Male
i(a). Under 18 Female
ii. 18-30 years Male
ii(a). 18-30 years Female
iii. 31-50 years Male
iii(a). 31-50 years Female
iv. 51 years or older Male
iv(a). 51 years old or older Female

I would like to write use a formula that would say "Calculate the number of
"51 years or older" males who have received rental assistance." and also
"Calculate the number of "51 years or older" males who have not received
rental assistance."

As I'm building this thing, I'm suing COUNTIF to calculate a total number of
people who fall within each measured criteria. I tried adding another
criteria to COUNTIF, but that didn't seem to work.

Can anyone help?

Try using SUMPRODUCT.

Where your COUNTIF formula would look something like

=COUNTIF(test_range,value)

you would use

=SUMPRODUCT(--(test_range=value))

To add another condition, use

=SUMPRODUCT(--(test_range=value),--(test_range_2=value2))

IF the value is a string, enclose in quotation marks.

Unlike COUNTIF, SUMPRODUCT does not use whole columns, you have to specify
the range. Also, test_range and test_range2 must be the same number of rows.


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Danny_McCaslin" wrote in message
...
I've created the beginnings of an Excel database to try to make our
end-of-year APR easier. We run a housing assistace program among other
things, and I need a little help, and, finding help here before, I figured
I
would try again.

With our APR, we have to keep track of demographic data conditional on
whether the customer has received housing assistance. I could just create
two
database spreadsheets for Rental Assistance and Supportive Services. I
would
rather designate the one database to create two different demographic
counts.
One for rental assistance, and one for those who did not receive rental
assistance.

For example:
Age
i. Under 18 Male
i(a). Under 18 Female
ii. 18-30 years Male
ii(a). 18-30 years Female
iii. 31-50 years Male
iii(a). 31-50 years Female
iv. 51 years or older Male
iv(a). 51 years old or older Female

I would like to write use a formula that would say "Calculate the number
of
"51 years or older" males who have received rental assistance." and also
"Calculate the number of "51 years or older" males who have not received
rental assistance."

As I'm building this thing, I'm suing COUNTIF to calculate a total number
of
people who fall within each measured criteria. I tried adding another
criteria to COUNTIF, but that didn't seem to work.

Can anyone help?

Don't know why Bob didn't give you the link to the web page he authored on
this function.

It's used by many as sort of like the bible on the subject.

Never known him to be shy.<bg

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Bob Phillips" wrote in message
...
Try using SUMPRODUCT.

Where your COUNTIF formula would look something like

=COUNTIF(test_range,value)

you would use

=SUMPRODUCT(--(test_range=value))

To add another condition, use

=SUMPRODUCT(--(test_range=value),--(test_range_2=value2))

IF the value is a string, enclose in quotation marks.

Unlike COUNTIF, SUMPRODUCT does not use whole columns, you have to specify
the range. Also, test_range and test_range2 must be the same number of rows.


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Danny_McCaslin" wrote in message
...
I've created the beginnings of an Excel database to try to make our
end-of-year APR easier. We run a housing assistace program among other
things, and I need a little help, and, finding help here before, I figured
I
would try again.

With our APR, we have to keep track of demographic data conditional on
whether the customer has received housing assistance. I could just create
two
database spreadsheets for Rental Assistance and Supportive Services. I
would
rather designate the one database to create two different demographic
counts.
One for rental assistance, and one for those who did not receive rental
assistance.

For example:
Age
i. Under 18 Male
i(a). Under 18 Female
ii. 18-30 years Male
ii(a). 18-30 years Female
iii. 31-50 years Male
iii(a). 31-50 years Female
iv. 51 years or older Male
iv(a). 51 years old or older Female

I would like to write use a formula that would say "Calculate the number
of
"51 years or older" males who have received rental assistance." and also
"Calculate the number of "51 years or older" males who have not received
rental assistance."

As I'm building this thing, I'm suing COUNTIF to calculate a total number
of
people who fall within each measured criteria. I tried adding another
criteria to COUNTIF, but that didn't seem to work.

Can anyone help?

Thanks a lot. I'm on my way to really getting this thing to work, but I have
one more problem. Can I do SUMPRODUCT and have it add integers? For instance,
One category is for "Number of dependents in household". Can I get it to add
those ONLY if I've put a "yes" in the rental assistance column, or do I have
to use a different formula for that?

"Bob Phillips" wrote:

Try using SUMPRODUCT.

Where your COUNTIF formula would look something like

=COUNTIF(test_range,value)

you would use

=SUMPRODUCT(--(test_range=value))

To add another condition, use

=SUMPRODUCT(--(test_range=value),--(test_range_2=value2))

IF the value is a string, enclose in quotation marks.

Unlike COUNTIF, SUMPRODUCT does not use whole columns, you have to specify
the range. Also, test_range and test_range2 must be the same number of rows.


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Danny_McCaslin" wrote in message
...
I've created the beginnings of an Excel database to try to make our
end-of-year APR easier. We run a housing assistace program among other
things, and I need a little help, and, finding help here before, I figured
I
would try again.

With our APR, we have to keep track of demographic data conditional on
whether the customer has received housing assistance. I could just create
two
database spreadsheets for Rental Assistance and Supportive Services. I
would
rather designate the one database to create two different demographic
counts.
One for rental assistance, and one for those who did not receive rental
assistance.

For example:
Age
i. Under 18 Male
i(a). Under 18 Female
ii. 18-30 years Male
ii(a). 18-30 years Female
iii. 31-50 years Male
iii(a). 31-50 years Female
iv. 51 years or older Male
iv(a). 51 years old or older Female

I would like to write use a formula that would say "Calculate the number
of
"51 years or older" males who have received rental assistance." and also
"Calculate the number of "51 years or older" males who have not received
rental assistance."

As I'm building this thing, I'm suing COUNTIF to calculate a total number
of
people who fall within each measured criteria. I tried adding another
criteria to COUNTIF, but that didn't seem to work.

Can anyone help?

"RagDyeR" wrote in message
...
Don't know why Bob didn't give you the link to the web page he authored on
this function.

It's used by many as sort of like the bible on the subject.

Never known him to be shy.<bg

I resent that! I don't deny it, but ... <g

I just thought a more concise explanation was needed by the OP in this case
Rick. You know how it is, it is nice sometimes to change the words.