| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count functions with multiple conditions/ranges
I'm trying to count the number of instances a "house" for example in B1
falls within a "rent" value of between 20 and <32 in D1 and need to change instances to "flat" and ranges, please see example below Type RENT House 58.98 Flat 31.50 Flat 36.50 answer would be count of 1 (flat with rent greater than 20 and less than 32 i'm trying to use Sumproduct(--(B1:B500="house"),--(D1:D500="<32")),--(D1:D500="20")) just by reading other peop[les posts but am unable to get it to work despite changing the formula slightly can anyone please help, greatly appreciated thankyou in advance |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count functions with multiple conditions/ranges
Remove your quotes on your numerical values......
=SUMPRODUCT(--(D1:D500="House"),--(E1:E500<32),--(E1:E50020)) -- John C "kEV" wrote: I'm trying to count the number of instances a "house" for example in B1 falls within a "rent" value of between 20 and <32 in D1 and need to change instances to "flat" and ranges, please see example below Type RENT House 58.98 Flat 31.50 Flat 36.50 answer would be count of 1 (flat with rent greater than 20 and less than 32 i'm trying to use Sumproduct(--(B1:B500="house"),--(D1:D500="<32")),--(D1:D500="20")) just by reading other peop[les posts but am unable to get it to work despite changing the formula slightly can anyone please help, greatly appreciated thankyou in advance |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count functions with multiple conditions/ranges
=SUMPRODUCT(--(A2:A50="House"),--(B2:B5020),--(B2:B20<32))
-- Regards, Peo Sjoblom "kEV" wrote in message ... I'm trying to count the number of instances a "house" for example in B1 falls within a "rent" value of between 20 and <32 in D1 and need to change instances to "flat" and ranges, please see example below Type RENT House 58.98 Flat 31.50 Flat 36.50 answer would be count of 1 (flat with rent greater than 20 and less than 32 i'm trying to use Sumproduct(--(B1:B500="house"),--(D1:D500="<32")),--(D1:D500="20")) just by reading other peop[les posts but am unable to get it to work despite changing the formula slightly can anyone please help, greatly appreciated thankyou in advance |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count functions with multiple conditions/ranges
thats great thankyou both very much, however, now i would like to sum all of
the amounts that fall between 20 and 32 for each condition i.e flat, house etc, can anyone help many thanks "kEV" wrote: I'm trying to count the number of instances a "house" for example in B1 falls within a "rent" value of between 20 and <32 in D1 and need to change instances to "flat" and ranges, please see example below Type RENT House 58.98 Flat 31.50 Flat 36.50 answer would be count of 1 (flat with rent greater than 20 and less than 32 i'm trying to use Sumproduct(--(B1:B500="house"),--(D1:D500="<32")),--(D1:D500="20")) just by reading other peop[les posts but am unable to get it to work despite changing the formula slightly can anyone please help, greatly appreciated thankyou in advance |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple IF functions, different ranges | Excel Discussion (Misc queries) | |||
| Use OFFSET and COUNT functions within Named Ranges | Excel Discussion (Misc queries) | |||
| Multiple Functions and conditions | Excel Worksheet Functions | |||
| sum of multiple ranges andmultiple conditions... | Excel Discussion (Misc queries) | |||
| COUNTIF Statement with Multiple Conditions in Different Ranges | Excel Worksheet Functions | |||
Hybrid Mode
