Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# I need an excel formula

Posted on 2011-03-24
Medium Priority
262 Views
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
Question by:wrt1mea
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 35209783
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

LVL 1

Author Closing Comment

ID: 35209900
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
###### Suggested Courses
Course of the Month7 days, 19 hours left to enroll