# Counting occurrences in a specified range and date

#### kebabpete

##### New Member
I'm currently using... =SUMPRODUCT((Quantity)*(MONTH(Date)=7)*(Adjustment_Type="Booking In Error"))

... to calculate the sum of a column based on a specified date range (MONTH(Date)=7) and also based on a specific criteria (Adjustment_Type).

Instead of finding the sum of the 'Quantity', can someone tell me what change to make to this formula to only count the number of occurrences within the same criteria?

Assume...
Column B = 'Quantity'
Column C = 'Date'

Thanks!

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
What version do you have? If 2007 or later try the Countifs formula, if i'm reading your formula correct just lose the quantity part from what you already have, or replace with Quantity<>"" which will make a count everytime theres a number in that colum wher the criterias met, if the default is 0 in the cell, swap the "" for >o

Last edited:
Hmmm, May have confused myself a bit now. Using 2007... Have just tried... =COUNTIFS("<>",Month(Date)=7,">0",Adjustment_Type="Despatch Error"). If I remove the ">0" then there is too few arguments.

I'm currently using... =SUMPRODUCT((Quantity)*(MONTH(Date)=7)*(Adjustment_Type="Booking In Error"))

... to calculate the sum of a column based on a specified date range (MONTH(Date)=7) and also based on a specific criteria (Adjustment_Type).

Instead of finding the sum of the 'Quantity', can someone tell me what change to make to this formula to only count the number of occurrences within the same criteria?

Assume...
Column B = 'Quantity'
Column C = 'Date'

Thanks!

your formula will give count with your specified criteria if you just remove the sum range at end in your sumproduct formula

I'm currently using... =SUMPRODUCT((Quantity)*(MONTH(Date)=7)*(Adjustment_Type="Booking In Error"))

... to calculate the sum of a column based on a specified date range (MONTH(Date)=7) and also based on a specific criteria (Adjustment_Type).

Instead of finding the sum of the 'Quantity', can someone tell me what change to make to this formula to only count the number of occurrences within the same criteria?

Assume...
Column B = 'Quantity'
Column C = 'Date'

Thanks!

Try

M.

Try

M.

Nailed it! Thanks for that.

Replies
9
Views
119
Replies
7
Views
556
Replies
3
Views
482
Replies
4
Views
136
Replies
0
Views
208

1,203,076
Messages
6,053,396
Members
444,661
Latest member
liamoohay

### 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.

### Which adblocker are you using?

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

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