Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting cells (COUNTIF) based on two or more criteria | Excel Worksheet Functions | |||
Counting Values that meet another cells criteria | Excel Worksheet Functions | |||
Counting specific text in a cell | Excel Worksheet Functions | |||
counting instances of words in a worksheet cell | Excel Discussion (Misc queries) | |||
Counting instances in a cell | Excel Discussion (Misc queries) |