My brain is melting - no idea for a title

[silentcoates]

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

 

[NBVC]

For each reason:

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

adjust ranges as necessary.

 

[Andrew Poulsom]

Try eg:

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

which you can copy down.

 

[silentcoates]

this does not work, is the formula finished?

 

[NBVC]

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

 

[silentcoates]

still not working, weird

 

[Andrew Poulsom]

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:

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

 

[njimack]

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

 

[NBVC]

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:

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

Thanks Andrew....a valuable tip

 

[tios]

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