SUMIFS help

Here is a formula you can put in D56 (or any open Column starting in row 56) and copy down:

=IF(AND(A56=$A$77,C56>0),$C$77/C56)

Are you trying to see this on an individual basis? If so, this formula will work. If not see below.

What if multiple records match the criteria, how would you like this to be handled?

If you have multiple records matching the criteria do you want: C77/(sum of values that meets criteria)?

If you just want to get one number based on all records that match the criteria use this:

=C77/SUMPRODUCT(--($A$56:$A$67=A77),--($C$56:$C$67>0),$C$56:$C$67)

Thanks, the second formula is exactly what I wanted (slightly adapted it).

How would I also add that 0 should be returned if EITHER $C$56:$C$67>0, OR C77=0? At present I just have IF $C$56:$C$67>0.

Dan

=C77/SUMPRODUCT(--($A$56:$A$67=A77),--($C$56:$C$67>0),$C$56:$C$67)

Actually, made a mistake in the last request and just realised at present the formula above just makes the C77/$C$56:$C$67 calculation if it finds a date MATCH [ ($A$56:$A$67=A77) ] and if ($C$56:$C$67>0).

How can I add the condition that the formula must return 0 if the matched row in $C$56:$C$67=0 OR C77=0. [Only one of them needs to =0 for the formula to return 0].

Dan

=IF(COUNTIF($C$56:$C$67,0),0,C77/SUMPRODUCT(--($A$56:$A$67=A77),--($C$56:$C$67>0),$C$56:$C$67))

See if this works for you, I didn't test it.

=IF(COUNTIF($C$56:$C$67,0),0,C77/SUMPRODUCT(--($A$56:$A$67=A77),--($C$56:$C$67>0),$C$56:$C$67))

The formula above is returning 0. The formula should find a match for ($A$56:$A$67=A77) and also find that C77 and C57 are both >0 and therefore make the C77/C57 calculation, which should return 7.18%.

The original formula below returns 7.18% (the correct answer).

=C77/SUMPRODUCT(--($A$56:$A$67=A77),--($C$56:$C$67>0),$C$56:$C$67)

oops, sorry I forgot an important part...

=IF(COUNTIF($C$56:$C$67,0)>0,0,C77/SUMPRODUCT(--($A$56:$A$67=A77),--($C$56:$C$67>0),$C$56:$C$67))


If any of the range C56:C67 are equal to 0, your formula will return 0.

I'm not sure if this formula is an array formula (if it doesn't work like this, try entering it using Ctrl+shift+Enter)

Thanks for your help Desu, it's almost what I was after. Instead of the formula returning 0 if any of the
range C56:C67 are equal to 0, I want it to return 0 only if the matched row in range C56:C67 is equal to 0. In this example
the matched row is C57. I'll explain further and I've added the two tables to make things clearer.

Here's the formula you've created so far for reference:
=IF(COUNTIF($C$56:$C$67,0)>0,0,C77/SUMPRODUCT(--($A$56:$A$67=A77),--($C$56:$C$67>0),$C$56:$C$67))

<table width="534" border="0" cellpadding="0" cellspacing="0"><col style="width: 37pt;" width="49"> <col style="width: 61pt;" width="81"> <col style="width: 58pt;" width="77"> <col style="width: 78pt;" width="104"> <col style="width: 82pt;" width="109"> <col style="width: 86pt;" width="114"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 37pt;" width="49" height="20"> </td> <td class="xl65" style="border-left: medium none; width: 61pt;" width="81">A</td> <td class="xl65" style="border-left: medium none; width: 58pt;" width="77">B</td> <td class="xl65" style="border-left: medium none; width: 78pt;" width="104">C</td> <td class="xl65" style="border-left: medium none; width: 82pt;" width="109">D</td> <td class="xl65" style="border-left: medium none; width: 86pt;" width="114">E</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">56</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Sep-10</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 416.91 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 230.02 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 230.02 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">57</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Oct-10</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 1,894.28 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 753.34 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 753.34 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">58</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Nov-10</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 1,382.71 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 2,001.68 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 2,001.68 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">59</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Dec-10</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 2,127.31 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 3,590.05 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 3,590.05 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">60</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Jan-11</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 1,152.42 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 1,090.97 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 1,087.75 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">61</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Feb-11</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 1,770.75 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 3,287.68 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 2,249.29 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">62</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Mar-11</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 1,261.16 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 773.33 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 593.49 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">63</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Apr-11</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 1,566.67 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 2,248.77 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 1,618.81 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">64</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">May-11</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 1,455.26 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 1,829.41 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 1,454.32 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">65</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Jun-11</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 2,324.57 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 5,025.41 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 3,982.14 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">66</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Jul-11</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 1,693.51 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 1,056.87 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 1,297.45 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">67</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Aug-11</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ - </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ - </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ - </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20"> </td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 17,045.55 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 21,887.53 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 18,858.32 </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20"> </td> <td class="xl65" style="border-left: medium none;">A</td> <td class="xl65" style="border-left: medium none;">B</td> <td class="xl65" style="border-left: medium none;">C</td> <td class="xl65" style="border-left: medium none;">D</td> <td class="xl65" style="border-left: medium none;">E</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">76</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Sep-10</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ - </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ - </td> <td class="xl69" style="border-top: medium none; border-left: medium none;">0.00%</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">77</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Oct-10</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 135.98 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 3.39 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;">0.00%</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">78</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Nov-10</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 46.75 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 161.50 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;">3.38%</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">79</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Dec-10</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 87.33 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 179.50 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;">4.11%</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">80</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Jan-11</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ - </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ - </td> <td class="xl69" style="border-top: medium none; border-left: medium none;">0.00%</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">81</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Feb-11</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ - </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ - </td> <td class="xl69" style="border-top: medium none; border-left: medium none;">0.00%</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">82</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Mar-11</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ - </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ - </td> <td class="xl69" style="border-top: medium none; border-left: medium none;">0.00%</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">83</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Apr-11</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 81.16 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 61.50 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;">5.18%</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">84</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">May-11</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 57.80 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;">-£ 84.50 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;">3.97%</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">85</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Jun-11</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 303.16 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;">-£ 195.78 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;">13.04%</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">86</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Jul-11</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 249.45 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 213.45 </td> <td class="xl69" style="border-top: medium none; border-left: medium none;">14.73%</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">87</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Aug-11</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Alias</td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ - </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ - </td> <td class="xl69" style="border-top: medium none; border-left: medium none;">#DIV/0!</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20"> </td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 961.63 </td> <td class="xl68" style="border-top: medium none; border-left: medium none;"> £ 339.06 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> </tr> </tbody></table>

The formula is in E77 on the lower table.
-It should look in Column A and realise that the Month/Year on the same row is
Oct-10.
-It then looks for a MATCH of Oct-10 in Column A in the table above.
-It finds a MATCH in Row 57.
-It then makes the calculation: C77/C57 (£135.98/£1894.28) [these are the two corresponding rows to the Date matches found]
-It only makes the calculation if either C77 or C57 are >0
-IF either C77 or C57 are equal to 0, then return 0

At present the formula is returning 0.00% when it should be 7.18% (E77). This is because C67 (the corresponding row for Aug-11
is equal to 0.

I could just use this formula:
=C77/SUMPRODUCT(--($A$56:$A$67=A77),--($C$56:$C$67>0),$C$56:$C$67)

but it has returned #DIV/0! in E87 as either C67 or C87 are equal to 0 for the Aug-11 row.

Any more ideas.

Dan

I've just received a working solution from help on another excel forum. The solution was:

=IF(INDEX($C$56:$C$67,MATCH(A77,$A$56:$A$67,0))>0,C77/INDEX($C$56:$C$67,MATCH(A77,$A$56:$A$67,0)),0)

Thanks anyway for your help Desu and any other contributors, much appreciated.

Dan