Count functions with multiple conditions/ranges

  • Thread starter Thread starter kEV
  • Start date Start date
Remove your quotes on your numerical values......
=SUMPRODUCT(--(D1:D500="House"),--(E1:E500<32),--(E1:E500>20))


--
John C


kEV said:
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
Click to expand...
Click to expand...
 
=SUMPRODUCT(--(A2:A50="House"),--(B2:B50>20),--(B2:B20<32))




--


Regards,


Peo Sjoblom

kEV said:
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
Click to expand...
Click to expand...
 
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 said:
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
Click to expand...
Click to expand...
 
Back
Top