software

Hi

Can anyone help?

I have a basic logging sheet, which is recording reports in and out of
my department. Column E shows which sub-team they went to ie
Transport, Engineering or Construction. Column G is the date out
column. I have a separate table with an overview per team.

The overview table lists the teams down the left, and then working
across the next column counts the number of reports for each team -
easy enough with a basic =COUNTIF(E5:E250,quot;Engineeringquot;) etc......
However in the next column of my overview I want to count those reports
completed per team by counting if there is a date in column G, but
against the appropriate team. Any ideas? Probably dead simple - but I
can't find how to do it!

Thanks

HDV--
HDV
------------------------------------------------------------------------
HDV's Profile: www.excelforum.com/member.php...oamp;userid=26299
View this thread: www.excelforum.com/showthread...hreadid=536989Hi!

Try this:

=SUMPRODUCT(--(Range1=quot;Engineeringquot;),--(ISNUMBER(Range2)))

Biff

quot;HDVquot; gt; wrote in message
...
gt;
gt; Hi
gt;
gt; Can anyone help?
gt;
gt; I have a basic logging sheet, which is recording reports in and out of
gt; my department. Column E shows which sub-team they went to ie
gt; Transport, Engineering or Construction. Column G is the date out
gt; column. I have a separate table with an overview per team.
gt;
gt; The overview table lists the teams down the left, and then working
gt; across the next column counts the number of reports for each team -
gt; easy enough with a basic =COUNTIF(E5:E250,quot;Engineeringquot;) etc......
gt; However in the next column of my overview I want to count those reports
gt; completed per team by counting if there is a date in column G, but
gt; against the appropriate team. Any ideas? Probably dead simple - but I
gt; can't find how to do it!
gt;
gt; Thanks
gt;
gt; HDV
gt;
gt;
gt; --
gt; HDV
gt; ------------------------------------------------------------------------
gt; HDV's Profile:
gt; www.excelforum.com/member.php...oamp;userid=26299
gt; View this thread: www.excelforum.com/showthread...hreadid=536989
gt;

It works - how does it work though I'm bamboozled. - thanks Biff.

HDV --
HDV
------------------------------------------------------------------------
HDV's Profile: www.excelforum.com/member.php...oamp;userid=26299
View this thread: www.excelforum.com/showthread...hreadid=536989Take a look at this site to help you understand sumproduct.

www.xldynamic.com/source/xld....T.html#classic

HTH
Regards,
Howard

quot;HDVquot; gt; wrote in message
...
gt;
gt; Hi
gt;
gt; Can anyone help?
gt;
gt; I have a basic logging sheet, which is recording reports in and out of
gt; my department. Column E shows which sub-team they went to ie
gt; Transport, Engineering or Construction. Column G is the date out
gt; column. I have a separate table with an overview per team.
gt;
gt; The overview table lists the teams down the left, and then working
gt; across the next column counts the number of reports for each team -
gt; easy enough with a basic =COUNTIF(E5:E250,quot;Engineeringquot;) etc......
gt; However in the next column of my overview I want to count those reports
gt; completed per team by counting if there is a date in column G, but
gt; against the appropriate team. Any ideas? Probably dead simple - but I
gt; can't find how to do it!
gt;
gt; Thanks
gt;
gt; HDV
gt;
gt;
gt; --
gt; HDV
gt; ------------------------------------------------------------------------
gt; HDV's Profile:
gt; www.excelforum.com/member.php...oamp;userid=26299
gt; View this thread: www.excelforum.com/showthread...hreadid=536989
gt;
Hi!

Try this little exercise:

Create this small table:

...........A...................B
1.......Eng..........4/27/2006
2.......Const.......3/30/2006
3.......Eng...........Pending
4.......Trans........Pending
5.......Eng...........(empty)

Now lets break down the formula into its individual parts:

=SUMPRODUCT(--(A1:A5=quot;Engquot;),--(ISNUMBER(B1:B5)))

Enter this formula in D1 and copy down to D5:

=--(A1=quot;Engquot;)

Enter this formula in E1 and copy down to E5:

=--ISNUMBER(B1)

Enter this formula F1 and copy down to F5:

=D1*E1

And finally, enter this formula in G1:

=SUM(F1:F5)

That's what's happening with this formula:

=SUMPRODUCT(--(A1:A5=quot;Engquot;),--(ISNUMBER(B1:B5)))

Dates are really just numbers that are formatted to look like dates. So, to
test if a date is present all you need to do is test the cell to see if it
contains a number, thus ISNUMBER.

The quot;--quot; double unary is used to convert boolean values: TRUE or FALSE to
numeric values 1 or 0:

(A1=quot;Engquot;) will return either TRUE or FALSE

--(A1=quot;Engquot;) will return either 1 or 0

Biff

quot;HDVquot; gt; wrote in message
...
gt;
gt; It works - how does it work though I'm bamboozled. - thanks Biff.
gt;
gt; HDV
gt;
gt;
gt; --
gt; HDV
gt; ------------------------------------------------------------------------
gt; HDV's Profile:
gt; www.excelforum.com/member.php...oamp;userid=26299
gt; View this thread: www.excelforum.com/showthread...hreadid=536989
gt;