Complex IF Function with Different Data Types

[sabledragonrook]

Hello all! Trying to pull something off here, but I'm not sure if it's possible. Below, 11/8/2015 is A9, 21 is C9, and 23 at the bottom there is C18.

[TABLE="width: 203"]
<tbody>[TR]
[TD="align: right"]11/8/2015[/TD]
[TD]Wordy[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]4.5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]4.5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]4.5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Max[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]300Editors[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD="align: right"]11/9/2015[/TD]
[TD]Wordy[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]23[/TD]
[/TR]
</tbody>[/TABLE]

From 21 down to 7 is one day's values. However, how many cells these values take up each day individually varies from day to day. I would like to create a complex IF function so that if the sum of all values occurring on a single day is >75, it will say "Budget Met" but if it is <75, it will say "Low Yield" in red. I can do all of this myself, except that I don't know how to account for the fact that, for example, on 11/8 there are 8 entries and on 11/9 there are 2. I was hoping there was a SUM function that could do the sum of all numerical values on X day. This is where I get a little lost, because if I'm just going to have to rewrite the IF function for the correct cells every day, I might as well do the math myself.

Help, anybody?

 

[AlanY]

this solution needs a helper column (D) that can be hidden.

Excel 2012
	A	B	C	D	E
9	11/08/2015	Wordy	21	11/08/2015	Budget Met
10			33	11/08/2015
11			11	11/08/2015
12			4.5	11/08/2015
13			4.5	11/08/2015
14			4.5	11/08/2015
15		Max	10	11/08/2015
16		300Editors	7	11/08/2015
17	11/09/2015	Wordy	9	11/09/2015	Low Yield
18			23	11/09/2015
Sheet2

Cell Formulas
Range	Formula
D9	=IF(ISBLANK(A9),D8,A9)
E9	=IF(ISBLANK(A9),"",IF(SUMIF(D:D,A9,C:C)>=75,"Budget Met","Low Yield"))

 

[Mackers]

Hi sable

Alan's solution should sort you out, but I wanted to add that in general it's good practice to include the date on every single row, rather than only when it changes - this makes it much easier to write formulas aggregating your data (which is what you are doing here - basically saying sum column C on the basis that the date is suchandsuch). If you look at the helper column D in the solution, each row has the date directly next to the value you are summing in column C.

This also means that if you want to filter your data to look at, let's say, all dates during November 2015, you will see all of the rows rather than just the first row (as the filter will remove the blanks).

Best of luck

Mackers

 

[sabledragonrook]

this solution needs a helper column (D) that can be hidden.

Excel 2012

	A	B	C	D	E
Wordy	Budget Met
Max
300Editors
Wordy	Low Yield

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]9[/TD]
[TD="align: right"]11/08/2015[/TD]

[TD="align: right"]21[/TD]
[TD="align: right"]11/08/2015[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]11/08/2015[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]11/08/2015[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"]11/08/2015[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"]11/08/2015[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"]11/08/2015[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]11/08/2015[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]11/08/2015[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]11/09/2015[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]11/09/2015[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]11/09/2015[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D9[/TH]
[TD="align: left"]=IF(ISBLANK(A9),D8,A9)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E9[/TH]
[TD="align: left"]=IF(ISBLANK(A9),"",IF(SUMIF(D:D,A9,C:C)>=75,"Budget Met","Low Yield"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks! It seems to not want to do the D9 formula correctly, though. It displays 42316 instead of the dates, and I'm not entirely sure why. Any ideas?

 

[AlanY]

you need to format column D as dates to display them properly.