Combine countifs with match for greater than or less than values

mmcmillin

New Member
Joined
Jan 8, 2015
Messages
5
I need to count up all the machines that are less than 181 days old and older than 149 days. I can do this with sumifs, but not countifs. The sumifs formula I used that worked is below. I just need a count and not a sum.

=SUMIFS(April!$O$2:$O$1080,April!$B$2:$B$1080,Sheet1!A3,April!$O$2:$O$1080,">149",April!$O$2:$O$1080,"<181")
 
Try

=COUNTIFS(April!$B$2:$B$1080,Sheet1!A3,April!$O$2:$O$1080,">149",April!$O$2:$O$1080,"<181")

M.
 
Upvote 0
I need a formula that can search for a location number on two spreadsheets (there are over 6,000 locations on each spreadsheet), which may be on each spreadsheet multiple times, then confirm the next column (representing one of several elements at that location) there may be 3-4 of this same element at each location, then I need the formula to return a response based on matching that location and that element and pull in the 1st outcome for that element at that location, 2nd outcome for the second of the same type of element at that location, then 3rd etc, then move down spreadsheet to the next type of element and then to the next location and set of elements. Help?

Example

[TABLE="width: 623"]
<colgroup><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Store[/TD]
[TD]element
[/TD]
[TD]last year's price
[/TD]
[TD][/TD]
[TD]Store [/TD]
[TD]element[/TD]
[TD]This year's price
[/TD]
[/TR]
[TR]
[TD]54662[/TD]
[TD]137 sm[/TD]
[TD]200[/TD]
[TD][/TD]
[TD]54667[/TD]
[TD]137 sm[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]54662[/TD]
[TD]137 sm[/TD]
[TD]250[/TD]
[TD][/TD]
[TD]54667[/TD]
[TD]137 sm[/TD]
[TD]275[/TD]
[/TR]
[TR]
[TD]54667[/TD]
[TD]137 sm[/TD]
[TD]300[/TD]
[TD][/TD]
[TD]54667[/TD]
[TD]137 sm[/TD]
[TD]315[/TD]
[/TR]
[TR]
[TD]54667[/TD]
[TD]137 sm[/TD]
[TD]300[/TD]
[TD][/TD]
[TD]54667[/TD]
[TD]137 sm[/TD]
[TD]310[/TD]
[/TR]
[TR]
[TD]54677[/TD]
[TD]137sm[/TD]
[TD]500[/TD]
[TD][/TD]
[TD]54677[/TD]
[TD]137sm[/TD]
[TD]510[/TD]
[/TR]
[TR]
[TD]54677[/TD]
[TD]137sm[/TD]
[TD]450[/TD]
[TD][/TD]
[TD]54677[/TD]
[TD]137sm[/TD]
[TD]460[/TD]
[/TR]
[TR]
[TD]54453[/TD]
[TD]85 sm[/TD]
[TD]300[/TD]
[TD][/TD]
[TD]54677[/TD]
[TD]85 sm[/TD]
[TD]310[/TD]
[/TR]
[TR]
[TD]54356[/TD]
[TD]85 sm[/TD]
[TD]300[/TD]
[TD][/TD]
[TD]54677[/TD]
[TD]85 sm[/TD]
[TD]315[/TD]
[/TR]
[TR]
[TD]54677[/TD]
[TD]12 sm[/TD]
[TD]250[/TD]
[TD][/TD]
[TD]54677[/TD]
[TD]12 sm[/TD]
[TD]270[/TD]
[/TR]
[TR]
[TD]54677[/TD]
[TD]24 sm[/TD]
[TD]175[/TD]
[TD][/TD]
[TD]54677[/TD]
[TD]24 sm[/TD]
[TD]180[/TD]
[/TR]
[TR]
[TD]54677[/TD]
[TD]32 sm[/TD]
[TD]180[/TD]
[TD][/TD]
[TD]54677[/TD]
[TD]32 sm[/TD]
[TD]190[/TD]
[/TR]
[TR]
[TD]55766[/TD]
[TD]137 sm[/TD]
[TD]250[/TD]
[TD][/TD]
[TD]55442[/TD]
[TD]85 sm[/TD]
[TD]260[/TD]
[/TR]
[TR]
[TD]55766[/TD]
[TD]137 sm[/TD]
[TD]275[/TD]
[TD][/TD]
[TD]55442[/TD]
[TD]85 sm[/TD]
[TD]285[/TD]
[/TR]
[TR]
[TD]55766[/TD]
[TD]137 sm[/TD]
[TD]275[/TD]
[TD][/TD]
[TD]55442[/TD]
[TD]70 sm [/TD]
[TD]285[/TD]
[/TR]
[TR]
[TD]55766[/TD]
[TD]55 sm[/TD]
[TD]150[/TD]
[TD][/TD]
[TD]55442[/TD]
[TD]12 sm[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]55766[/TD]
[TD]75 sm[/TD]
[TD]175[/TD]
[TD][/TD]
[TD]55442[/TD]
[TD]24 sm [/TD]
[TD]185[/TD]
[/TR]
[TR]
[TD]55766[/TD]
[TD]85 sm[/TD]
[TD]185[/TD]
[TD][/TD]
[TD]55442[/TD]
[TD]55 sm[/TD]
[TD]195[/TD]
[/TR]
[TR]
[TD]55766[/TD]
[TD]85 sm[/TD]
[TD]195[/TD]
[TD][/TD]
[TD]55442[/TD]
[TD]55 sm[/TD]
[TD]190[/TD]
[/TR]
[TR]
[TD]55766[/TD]
[TD]137 sm[/TD]
[TD]250[/TD]
[TD][/TD]
[TD]55442[/TD]
[TD]40 sm[/TD]
[TD]260[/TD]
[/TR]
[TR]
[TD]55442[/TD]
[TD]85 sm[/TD]
[TD]180[/TD]
[TD][/TD]
[TD]55766[/TD]
[TD]137 sm[/TD]
[TD]190[/TD]
[/TR]
[TR]
[TD]55442[/TD]
[TD]85 sm[/TD]
[TD]170[/TD]
[TD][/TD]
[TD]55766[/TD]
[TD]137 sm[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]55442[/TD]
[TD]70 sm [/TD]
[TD]160[/TD]
[TD][/TD]
[TD]55766[/TD]
[TD]137 sm[/TD]
[TD]180
[/TD]
[/TR]
[TR]
[TD]55442[/TD]
[TD]12 sm[/TD]
[TD]90[/TD]
[TD][/TD]
[TD]55766[/TD]
[TD]55 sm[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]55442[/TD]
[TD]24 sm [/TD]
[TD]130[/TD]
[TD][/TD]
[TD]55766[/TD]
[TD]75 sm[/TD]
[TD]140[/TD]
[/TR]
[TR]
[TD]55442[/TD]
[TD]55 sm[/TD]
[TD]150[/TD]
[TD][/TD]
[TD]55766[/TD]
[TD]85 sm[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]55442
[/TD]
[TD]55 sm[/TD]
[TD]155[/TD]
[TD][/TD]
[TD]55766[/TD]
[TD]85 sm[/TD]
[TD]175[/TD]
[/TR]
[TR]
[TD]55442[/TD]
[TD]40 sm[/TD]
[TD]140[/TD]
[TD][/TD]
[TD]55766[/TD]
[TD]137 sm[/TD]
[TD]155[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 235"]
<tbody>[TR]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"]

[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top