Counting Rows with multiple criteria, including >= date, and multiple OR conditions

jentrxm

New Member
Joined
Jul 31, 2017
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I am trying to count every row where the date is between the first and last days of a given month, and four other columns contain at least one of various values. I started with COUNTIF, but ran into trouble when it could only handle two OR conditions. Now I'm trying variations on SUMPRODUCT, but I can't get it figured out.

Here is my current formula, which is returning #VALUE!:

1612217164151.png


And here it is broken apart so you can better read each criteria section:

1612217103961.png


I've tried using ISNUMBER(MATCH(....with the various criteria too, but I'm not having any luck. Columns H, P, and Q are OR conditions where the value can be any of these. Column K must be Delivery.

I am very grateful for any help you can provide.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Welcome to MrExcel Message Board.
Please upload your example file & Desired Results with XL2BB ADDIN (Preferable) OR upload it at free uploading site e.g. www.dropbox.com or googledrive or onedrive and insert link here.
 
Upvote 0
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Thank you both for the feedback! I'm on Office 365, and have updated my profile. I'm on a work computer, though, so I can't install any add-ons.

Here's the table if I condensed all the columns down:

Must be =>5/1/2020
Must be <6/1/2020
DateFruit 1Fruit 2Card TypePickup or Delivery
5/13/2020ApplePineappleMCDelivery
5/17/2020ApplePearAmexPickup
6/5/2020OrangePineappleDiscoverDelivery

The expected output would be "1", because only the line with 5/13/2020 meets all the criteria. The 5/17 line wasn't Delivery, the 6/5 line was out of scope for the date and used the wrong credit card.
 
Upvote 0
Thanks for updating your profile & the sample data. (y)

See if you could adapt something like this. (Note that my dates are in d/m/y format)

21 02 09.xlsm
ABCDEFGH
1Must be =>1/05/2020Pickup/DeliveryDeliveryCount1
2Must be <1/06/2020Card TypeMC
3DateFruit 1Fruit 2Card TypePickup or Delivery
413/05/2020ApplePineappleMCDelivery
517/05/2020ApplePearAmexPickup
66/05/2020OrangePineappleDiscoverDelivery
Count Rows
Cell Formulas
RangeFormula
H1H1=COUNT(FILTER(A4:A6,(A4:A6>=B1)*(A4:A6<B2)*(E4:E6=E1)*(D4:D6=E2),""))
 
Upvote 0
@Peter_SSs I'm definitely going to play around with this. You have it set to filter Col D down to MC as the credit card type. How would this change if I wanted to count both MC and Amex?
 
Upvote 0
Actually I believe I figured it out with + signs. I'll update this in a bit.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

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