counting more than one criteria in one cell
 

In a worksheet I have two columns one has a type and the second has a number.
ex. RES(residential), COM(commercial) in column one and the corresponding
numbers 001,002 are both RES numerical types. I want to be able to count them
individually by the type they fall into. ex. RES has 1200 (type 001) and RES
also has 1000 (type 002). The result desired would be RES..... 001.....1200
and next serch in the next cell be RES.....002.......1000. I was trying to
use a countif(and statement and it would bring back to few arguments. Can you
help.
--
Maggie

try
=sumproduct(--(A1:A2000="Res"),--(B1:B2000="001"))
if the 001 is text format
=sumproduct(--(A1:A2000="Res"),(B1:B2000=1))
if the 001 is numeric.
and the same for 002

The range in the sumproduct needs to adjusted to cover the range you need.
You cannot just Select A:A and B:B.


"Maggie" wrote:

In a worksheet I have two columns one has a type and the second has a number.
ex. RES(residential), COM(commercial) in column one and the corresponding
numbers 001,002 are both RES numerical types. I want to be able to count them
individually by the type they fall into. ex. RES has 1200 (type 001) and RES
also has 1000 (type 002). The result desired would be RES..... 001.....1200
and next serch in the next cell be RES.....002.......1000. I was trying to
use a countif(and statement and it would bring back to few arguments. Can you
help.
--
Maggie

With the type RES and COM in say A1:A500 and the numbers 0001 and 0002 in
B1:B500, in other cells, (doesn't matter where) try

=SUMPRODUCT((A1:A500="RES")*(B1:B500="0001"))
and
=SUMPRODUCT((A1:A500="RES")*(B1:B500="0002"))

The quotes are necessary on 0001 as XL will strip out the leading zeros

Use the same in two more cells substituting "RES" for "COM"

Note that the two cell ranges, A1:A500 and B1:B500 must be of the same size.

Alternatively you could put the search criteria in convenient cells, RES in
say D1 and 0001 in D2 and use

=SUMPRODUCT((A1:A500=D1)*(B1:B500=D2))

Then you could change D1 to COM and D2 to 0002 etc and toggle the results
that way. Note that any cell containing a value like 0001 needs to be
formatted as Text to retain the leading zero's

Regards,
Alan.
"Maggie" wrote in message
...
In a worksheet I have two columns one has a type and the second has a
number.
ex. RES(residential), COM(commercial) in column one and the corresponding
numbers 001,002 are both RES numerical types. I want to be able to count
them
individually by the type they fall into. ex. RES has 1200 (type 001) and
RES
also has 1000 (type 002). The result desired would be RES.....
001.....1200
and next serch in the next cell be RES.....002.......1000. I was trying to
use a countif(and statement and it would bring back to few arguments. Can
you
help.
--
Maggie

This was perfect thank you for your information

"bj" wrote:

try
=sumproduct(--(A1:A2000="Res"),--(B1:B2000="001"))
if the 001 is text format
=sumproduct(--(A1:A2000="Res"),(B1:B2000=1))
if the 001 is numeric.
and the same for 002

The range in the sumproduct needs to adjusted to cover the range you need.
You cannot just Select A:A and B:B.


"Maggie" wrote:

In a worksheet I have two columns one has a type and the second has a number.
ex. RES(residential), COM(commercial) in column one and the corresponding
numbers 001,002 are both RES numerical types. I want to be able to count them
individually by the type they fall into. ex. RES has 1200 (type 001) and RES
also has 1000 (type 002). The result desired would be RES..... 001.....1200
and next serch in the next cell be RES.....002.......1000. I was trying to
use a countif(and statement and it would bring back to few arguments. Can you
help.
--
Maggie

Thank you for your assistance this was perfect

"Alan" wrote:

With the type RES and COM in say A1:A500 and the numbers 0001 and 0002 in
B1:B500, in other cells, (doesn't matter where) try

=SUMPRODUCT((A1:A500="RES")*(B1:B500="0001"))
and
=SUMPRODUCT((A1:A500="RES")*(B1:B500="0002"))

The quotes are necessary on 0001 as XL will strip out the leading zeros

Use the same in two more cells substituting "RES" for "COM"

Note that the two cell ranges, A1:A500 and B1:B500 must be of the same size.

Alternatively you could put the search criteria in convenient cells, RES in
say D1 and 0001 in D2 and use

=SUMPRODUCT((A1:A500=D1)*(B1:B500=D2))

Then you could change D1 to COM and D2 to 0002 etc and toggle the results
that way. Note that any cell containing a value like 0001 needs to be
formatted as Text to retain the leading zero's

Regards,
Alan.
"Maggie" wrote in message
...
In a worksheet I have two columns one has a type and the second has a
number.
ex. RES(residential), COM(commercial) in column one and the corresponding
numbers 001,002 are both RES numerical types. I want to be able to count
them
individually by the type they fall into. ex. RES has 1200 (type 001) and
RES
also has 1000 (type 002). The result desired would be RES.....
001.....1200
and next serch in the next cell be RES.....002.......1000. I was trying to
use a countif(and statement and it would bring back to few arguments. Can
you
help.
--
Maggie