Sum on Criteria in Two Columns

[bwiebe]

I wish to add values in a column if they fall within a numerical range in one column and if they match a text criteria in another column. I also wish to count cells which meet a numerical range criteria in one column and a text criteria from another column. See example

[Jonmo1]

Try
=SUMIFS($B$2:$B$15,$B$2:$B$15,">=0",$B$2:$B$15,"<=999",$A$2:$A$15,$D23)
and
=COUNTIFS($B$2:$B$15,">=0",$B$2:$B$15,"<=999",$A$2:$A$15,$D23)

Just change the 0 and 999 to 1000 and 4999 for each section.

And for the 10000+ remove the <= criteria

[José Augusto]

Hi
Use in E21, G21, I21, K21, M21 the values 0, 999, 4999, 9999, 9E99
In F23 use
=SUMPRODUCT(--($A$2:$A$15=$D23)*($B$2:$B$15>E$21)*($B$2:$B$15<=G$21)*$B$2:$B$15)
In G23 use
=SUMPRODUCT(--($A$2:$A$15=$D23)*($B$2:$B$15>E$21)*($B$2:$B$15<=G$21))
format as you witch
select F23 and G23 and copy down and forward.

[bwiebe]

Thank you kindly, Jonmo and Jose. This was very helpful and seems to be working fine in my spreadsheet.

[Jonmo1]

You're welcome.