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

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

Commented:
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

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