Formula for Workload Report

[kbaker]

I can use the formula below to sum the total "Types" with
the "Types Visit", e.g., there are 8 Type "11" which are
also Type Visit "IK" This formula works for me if it is on
the same worksheet. What I need is to create a report on
another worksheet TAB (same workbook) which carries these
totals in a master defined report.

Type Type Visit
11 D/IK
11 D/IK
11 IK
11 IK
11 IK
11 IK
11 IK
11 IK
11 IK
11 IK
=SUM(IF($D$2:$D$96="11", IF($F$2:$F$96="IK",1,0)))

Can someone help? Ken B

 

[Felipe]

Ken,

I typed this in a cell in Sheet2:
=SUMPRODUCT((Sheet1!A25:A30=11)*(Sheet1!B25:B30="IK"))

Replace Sheet2, A25:A30 and B25:B30 as needed.

Regards,
Felipe

 

[Biff]

Hi kbaker,

Use Sumproduct instead:

=SUMPRODUCT((Sheet1!D296=11)*(Sheet1!F2:F96="IK"))

Biff

 

[Ken Wright]

Pivot Pivot Pivot Table!!!!! Debra Dalgleish's intro to the subject:-

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm


Failing that you could sort your data in the first column and then use Data / Subtotals.


Failing that, and to finally answer your question, try using SUMPRODUCT which will allow you to
specify two variables in the conditions, eg:-

=SUMPRODUCT((Sheet2!$D$2:$D$96=11)*(Sheet2!$F$2:$F$96="IK"))