My brain is melting - no idea for a title

silentcoates

New Member
Joined
Oct 11, 2005
Messages
41
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))

adjust ranges as necessary.
 
Upvote 0
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
 
Upvote 0
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))
 
Upvote 0
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))
 
Upvote 0
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 :LOL:
 
Upvote 0
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
 
Upvote 0

Forum statistics

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