Count Muliple Fields

[Steven1985]

Hi,

I need to SUM IF and count multiple fields.

I need to SUM if a 'RENTAL' on TAB 2 Column A40, Took 30 mins TAB 1 Column BC to display in TAB 2 Column C40


The formula I was using was -
=SUM(IF('TAB1'!E4:E1001=Summary!A40,IF('Alert Spreadsheet'!E4:E1001,'Alert Spreadsheet'!BC4:BC1001)))


Eg -
TAB 2

Column A40 ColumnC40

<TABLE style="WIDTH: 468pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=623 border=0><COLGROUP><COL style="WIDTH: 158pt; mso-width-source: userset; mso-width-alt: 7716" width=211><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><COL style="WIDTH: 133pt; mso-width-source: userset; mso-width-alt: 6473" width=177><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 158pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #dbeef3" width=211 height=20>Alert</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 83pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbeef3" width=110>Volume Worked</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 94pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbeef3" width=125>Time Taken (Mins)</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 133pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbeef3" width=177>Average (Mins) Per Case</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Rental</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">60</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0.00</TD></TR></TBODY></TABLE>

TAB1
Column E Column BC

<TABLE style="WIDTH: 150pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=200 border=0><COLGROUP><COL style="WIDTH: 102pt; mso-width-source: userset; mso-width-alt: 4973" width=136><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 63.75pt" height=85><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 102pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 63.75pt; BACKGROUND-COLOR: #ccffcc" width=136 height=85>Category</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=64> Time taken to work case (Mins)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Rental </TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>30</TD></TR></TBODY></TABLE>

Hope this makes sense

Thanks
Steve

 

[Andrew Poulsom]

Your formula refers to 3 worksheets.

 

[Steven1985]

Sorry, it's -

=SUM(IF('TAB1'!E4:E1001=TAB2!A40,IF('TAB1'!E4:E1001,'TAB1'!BC4:BC1001)))

 

[Andrew Poulsom]

Is it?

=SUM(IF(TAB1!E4:E1001=TAB2!A40,IF(TAB1!BC4:BC1001=30,TAB1!BC4:BC1001)))

confirmed with Ctrl+Shift+Enter.