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.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,687
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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’)
 

jentrxm

New Member
Joined
Jul 31, 2017
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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),""))
 

jentrxm

New Member
Joined
Jul 31, 2017
Messages
4
Office Version
  1. 365
Platform
  1. Windows
@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?
 

jentrxm

New Member
Joined
Jul 31, 2017
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Actually I believe I figured it out with + signs. I'll update this in a bit.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,207
Messages
5,629,294
Members
416,384
Latest member
frsamiee

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
Top