• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

I need an excel formula

I need an excel formula to correctly count a total number and a total length amount (please see the attached file).

What I am trying to do is:

1. Count a total number that has dates in either Column H (Signed), Column I (Closed Date) or Column M (Approval) for a specific date range (See the Info tab Columns A and B). I need to make certain that it only counts it as one if there is a date in 3 of 3 places or 2 of 3 places.

2. I am also trying to sum up the amount that is in Column J (Actual Length) on the info tab, if there is a date in either Column H (Signed), Column I (Closed Date) or Column M (Approval). I need to make certain that it only adds the length one time, not 3 times.

I am trying to use a formula that I got here, only messaged to this particular issue, only its counting something way wrong. The Info Tab has the formulas. The data tab has the, well , data.
3-24-11.xlsx
0
wrt1mea
Asked:
wrt1mea
1 Solution
 
barry houdiniCommented:
Try this for C2 copied down

=SUMPRODUCT(((Data!$H$2:$H$10000>=$A2)*(Data!$H$2:$H$10000<=$B2)+(Data!$I$2:$I$10000>=$A2)*(Data!$I$2:$I$10000<=$B2)+(Data!$M$2:$M$10000>=$A2)*(Data!$M$2:$M$10000<=$B2)>0)*(Data!$K$2:$K$10000="N"))

and exactly the same for D2 except with added sum range

=SUMPRODUCT(((Data!$H$2:$H$10000>=$A2)*(Data!$H$2:$H$10000<=$B2)+(Data!$I$2:$I$10000>=$A2)*(Data!$I$2:$I$10000<=$B2)+(Data!$M$2:$M$10000>=$A2)*(Data!$M$2:$M$10000<=$B2)>0)*(Data!$K$2:$K$10000="N"),Data!$J$2:$J$10000)

see attached

regards, barry
26909642.xlsx
0
 
wrt1meaAuthor Commented:
Works perfectly! I am trying to learn and undertand these as I go along, you seem to be one of the best in the busines!

Thanks again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now