SUM(IF...(IF...(IF.... question

[Prodiclson]

I am trying to sum a column based on multiple criteria... specifically a column of time (hh:mm:ss) IF the value is greater than 00:30:00 + IF the value in another column (same row) matches a specific text value in another cell + IF the value in another column matches the date... (I hope I explained this OK...??)

I am having some troubles doing this and wa hoping for a little help from some fellow tipsters....

 

[barry houdini]

Hello Prodiclson, welcome to MrExcel

You could use a SUMPRODUCT formula for this

Assuming your times are in column A, dates in B and text in C something like

=SUMPRODUCT((A1:A100>"0:30"+0)*(B1:B100=E2)*(C1:C100=F2),A1:A100)

Where E2 contains the date you want to match and F2 the specific text
I'm assuming you are summing the times, if so format result cell as [h]:mm....or are you summing another column?

 

[lenze]

Hi and welcome to the board!!

Have a look at SUMPRODUCT. Your syntax would be somthing like this.

=SUMPRODUCT(--(A2:A10>"00:30:"+0)*--(B2:B10 ="Text")*--(C2:C10+"Date"))

Post back more detail and we can give an exact solution.

lenze

 

[Prodiclson]

I will try to give some more details...
We have some equipment here in our plant, and I am just trying to be lazy (go figure) and have a downtime report generated as the data is input into the sheet by our operators. Each row represents a single batch if you will and the various columns represent details of the batch in question, and there are several batches per day. For the downtime, we have an "allowed" period of time inbetween batches of 30 minutes, with any additional time being considered downtime (non-operational time if you will). We have a few catagories of downtime and each is tracked and graphed togehter to indicate trends / issues we may have.

What I am really trying to do is to auto-fill a chart similar to this:
<TABLE style="WIDTH: 212pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=283 border=0 x:str><COLGROUP><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 987" width=27><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 20pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=27 height=18></TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>A</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>B</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>C</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>D</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Reason</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Reason</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Reason</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>2</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Date</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>3</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Date</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18 x:num>4</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">Date</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl32 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>

Where the data for say, B2 will come from another worksheet in the same file. In that worksheet, I would like to sum up the time inbetween batches [shown in D:5D379] IF the time is greater than 30 minutes + IF the date of the batch [shown in B5:B379] matches A2 + IF the reason for the delay [H5:H379] matches B1

Hope this helps a little bit more

 

[SmokeyVol]

I have a similar question....


My 2 variables:

• B column is the phase either being 1 or 2
• E column is the status either being SOLD or having a price

What I'm trying to SUM is total arceage (C column) sold in phase 1

I've tried this but it does not work =SUM(IF(B:B=1,IF(E:E="SOLD",C:C))) it's adding the entire column

 

[barry houdini]

....Where the data for say, B2 will come from another worksheet in the same file. In that worksheet, I would like to sum up the time inbetween batches [shown in D:5D379] IF the time is greater than 30 minutes + IF the date of the batch [shown in B5:B379] matches A2 + IF the reason for the delay [H5:H379] matches B1

Assuming your source data is on a worksheet called Data then your formula in B2 copied across and down could be

=SUMPRODUCT(('Data'!D5:D379>"0:30"+0)*('Data'!H5:H379=B$1)*('Data'!B5:B379=$A2),'Data'!D5:D379)

format all cells as [h]:mm

 

[barry houdini]

I have a similar question....

You can't use whole column references, try

=SUMPRODUCT((B1:B100=1)*(E1:E100="Sold"),C1:C100)

 

[Prodiclson]

Mr Houdini....
YOU ROCK !!!!
That has been kicking my butt all morning.....

 

[Prodiclson]

OK, I spoke a little bit too soon...
You do still rock Mr Houdini, but that formula did not do it for me.
I am not looking for a product (multiplication) but a simple sum with multiple conditions (I am not thoughly familiar with the SUMPRODUCT function, so maybe I am off base here)

The fomula is giving me a constant zero no matter the time shown....

 

[Kaps_mr2]

You could insert a helper column :-

E1 =IF(and (a1>0.02,b1= c1,date(d1)=date(02,08,2008))="MATCH","NO MATCH")

and then apply SUMIF to column E. This allows to manually confirm that individual rows meet your chosen criteria. Thanks

Kaps