# My brain is melting - no idea for a title

#### silentcoates

##### New Member
Hi

This has really got me stumped.

I have a list of twenty reasons starting from Cell H2. In column A I have a list of dates that I will manually enter over time. In column E i have a validation list box of the above twenty queries. In cells I2 and J2 I have two dates which I specify for reporting reasons.

I need a way of finding out how many times a reason appears in column E between two dates (I2 and J2) and enter the value next to that particular reason in column H.

Thanks, if you can help with this

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
For each reason:

=SUMPRODUCT(--(E1:E100="reason"),--(A1:A100>I2),--(A1:A100<J2))

Try eg:

=SUMPRODUCT(--(A\$1:A\$100>=I2),--(A\$1:A\$100<=J2),--(E\$1:E\$100=H2))

which you can copy down.

this does not work, is the formula finished?

I am not sure why it is cutting off.

Trying again...

=SUMPRODUCT(--(E1:E100="whatever reason"),--(A1:A100>I2),--(A1:A100<J2))

Edit: What's going on.....anyways...similar to Andrew's

still not working, weird

Sometimes the less than sign is treated as HTML by the Board. To avoid truncation surround it with spaces or put it in code tags:

Code:
``=SUMPRODUCT(--(E1:E100="reason"),--(A1:A100>I2),--(A1:A100<J2))``

First off, name some of your ranges, to make the formula easier to read. I'll assume you've used the following names:

Name your list of reasons in column E 'Reasons'
Name your list of dates in column A 'Dates
Name your start date in I2 'Start_Date'
Name your end date in J2 as 'End_Date'

In an empty cell (preferably in Row 2) enter the following, then copy down:

=SUMPRODUCT((Reasons=\$H2)*(Dates>Start_Date)*(Dates
<End_Date))

Andrew Poulsom said:
Sometimes the less than sign is treated as HTML by the Board. To avoid truncation surround it with spaces or put it in code tags:

Code:
``=SUMPRODUCT(--(E1:E100="reason"),--(A1:A100>I2),--(A1:A100<J2))``

Thanks Andrew....a valuable tip

Hi there

my very first attempt to come up with a solution but it sounds like you are looking at an array

To make this work I used your cell references but moved the dates from H2:I2 to I2:J2 (needed a space next to H2) and entered sample data in A2 to A41 (dates), E2 to E41 (reasons), H2 to H4 (reason codes)

={SUM((\$A\$2:\$A\$41>\$J\$2)*(\$A\$2:\$A\$41<\$K\$2)*(\$E\$2:\$E\$41=H2))}

not sure if you are familiar with Arrays but if not, don't type the {} use CTRL SHIFT and ENTER to enter the formulae (in cell I2)

obviously change the 41's for your range of data or better still use named ranges

Hope this helps

Replies
3
Views
416
Replies
0
Views
236
Replies
1
Views
321
Replies
10
Views
955
Replies
2
Views
355

1,212,059
Messages
6,105,659
Members
447,974
Latest member
misspancake

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