SUM IF has a Match

nostradamus

nostradamus

Board Regular
Joined
Aug 9, 2010
Messages
143
Office Version
  1. 365
Platform
  1. Windows
Hi I need some help on some Match problem
Below is a spreadsheet with data to be entered in Col A-D
Formulas needed in Col F.

1) F2 = if values in col A (all the way to the end, not just A11) match "Ephemeroptera" or "Trichoptera", then sum corresponding values from col D; else return value ZERO. Now, the formula should not return Error, in case data is only entered upto A5 or A95, as the amount of cells filled will vary depending on the quantity at hand (it will be 1000 rows at the max, if that is a limitation).

2) F3 = sum of "Ephemeroptera" divided by sum of "Trichoptera" from col A (actual values from col D).

3) F4 = percentage of "Ephemeroptera" = (sum of all values in D)/(sum of "Ephemeroptera" values Matched from col A)

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 11">****** name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5Cagrubh%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Font Definitions */ @font-face {font-family:Cambria; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:0; mso-generic-font-family:roman; mso-font-pitch:variable; mso-font-signature:-1610611985 1073741899 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> <table class="MsoNormalTable" style="width: 463.05pt; margin-left: 4.65pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="617"> <tbody><tr style="height: 12.75pt;"> <td style="width: 21.95pt; border: 1pt solid windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="29" nowrap="nowrap"> <o:p></o:p>
</td> <td style="width: 78pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color;" valign="bottom" width="104" nowrap="nowrap">
A<o:p></o:p>​
</td> <td style="width: 73.65pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color;" valign="bottom" width="98" nowrap="nowrap">
B<o:p></o:p>​
</td> <td style="width: 70.25pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color;" valign="bottom" width="94" nowrap="nowrap">
C<o:p></o:p>​
</td> <td style="width: 52.3pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color;" valign="bottom" width="70" nowrap="nowrap">
D<o:p></o:p>​
</td> <td style="width: 64.6pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="86">
E<o:p></o:p>​
</td> <td style="width: 102.3pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="136">
F<o:p></o:p>​
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 21.95pt; border-width: medium 1pt 1pt; border-style: none solid solid; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext;" x:num="" u4:num="" valign="bottom" width="29" nowrap="nowrap">
1<o:p></o:p>​
</td> <td style="width: 78pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" valign="bottom" width="104" nowrap="nowrap">
Order<o:p></o:p>​
</td> <td style="width: 73.65pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" valign="bottom" width="98" nowrap="nowrap">
Family<o:p></o:p>​
</td> <td style="width: 70.25pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" valign="bottom" width="94" nowrap="nowrap">
Genus<o:p></o:p>​
</td> <td style="width: 52.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" valign="bottom" width="70" nowrap="nowrap">
collected<o:p></o:p>​
</td> <td style="width: 64.6pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="86">
<o:p> </o:p>​

</td> <td style="width: 102.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="136">
<o:p> </o:p>​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 21.95pt; border-width: medium 1pt 1pt; border-style: none solid solid; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext;" x:num="" u4:num="" valign="bottom" width="29" nowrap="nowrap">
2<o:p></o:p>​
</td> <td style="width: 78pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:str="Ephemeroptera " u4:str="Ephemeroptera " valign="bottom" width="104" nowrap="nowrap"> Ephemeroptera <o:p></o:p>
</td> <td style="width: 73.65pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:str="Baetidae " u4:str="Baetidae " valign="bottom" width="98" nowrap="nowrap"> Baetidae <o:p></o:p>
</td> <td style="width: 70.25pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" valign="bottom" width="94" nowrap="nowrap"> Baetis<o:p></o:p>
</td> <td style="width: 52.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:num="" u4:num="" valign="bottom" width="70" nowrap="nowrap">
22<o:p></o:p>​
</td> <td style="width: 64.6pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="86">
E, T total​
</td> <td style="width: 102.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="136">
= sum of E, T values from Col D<o:p></o:p>​
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 21.95pt; border-width: medium 1pt 1pt; border-style: none solid solid; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext;" x:num="" u4:num="" valign="bottom" width="29" nowrap="nowrap">
3<o:p></o:p>​
</td> <td style="width: 78pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:str="Ephemeroptera " u4:str="Ephemeroptera " valign="bottom" width="104" nowrap="nowrap"> Ephemeroptera <o:p></o:p>
</td> <td style="width: 73.65pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:str="Baetidae " u4:str="Baetidae " valign="bottom" width="98" nowrap="nowrap"> Baetidae <o:p></o:p>
</td> <td style="width: 70.25pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" valign="bottom" width="94" nowrap="nowrap"> Dactylobaetis<o:p></o:p>
</td> <td style="width: 52.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:num="" u4:num="" valign="bottom" width="70" nowrap="nowrap">
102<o:p></o:p>​
</td> <td style="width: 64.6pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="86">
E/T ratio<o:p></o:p>​
</td> <td style="width: 102.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="136">
= E/T <o:p></o:p>​
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 21.95pt; border-width: medium 1pt 1pt; border-style: none solid solid; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext;" x:num="" u4:num="" valign="bottom" width="29" nowrap="nowrap">
4<o:p></o:p>​
</td> <td style="width: 78pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:str="Coleoptera " u4:str="Coleoptera " valign="bottom" width="104" nowrap="nowrap"> Coleoptera <o:p></o:p>
</td> <td style="width: 73.65pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:str="Elmidae " u4:str="Elmidae " valign="bottom" width="98" nowrap="nowrap"> Elmidae <o:p></o:p>
</td> <td style="width: 70.25pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" valign="bottom" width="94" nowrap="nowrap"> Ancyronyx<o:p></o:p>
</td> <td style="width: 52.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:num="" u4:num="" valign="bottom" width="70" nowrap="nowrap">
21<o:p></o:p>​
</td> <td style="width: 64.6pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="86">
% E<o:p></o:p>​
</td> <td style="width: 102.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="136">
= Percent of E<o:p></o:p>​
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 21.95pt; border-width: medium 1pt 1pt; border-style: none solid solid; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext;" x:num="" u4:num="" valign="bottom" width="29" nowrap="nowrap">
5<o:p></o:p>​
</td> <td style="width: 78pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:str="Trichoptera " u4:str="Trichoptera " valign="bottom" width="104" nowrap="nowrap"> Trichoptera <o:p></o:p>
</td> <td style="width: 73.65pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:str="Hydrobiosidae " u4:str="Hydrobiosidae " valign="bottom" width="98" nowrap="nowrap"> Hydrobiosidae <o:p></o:p>
</td> <td style="width: 70.25pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" valign="bottom" width="94" nowrap="nowrap"> Atopsyche<o:p></o:p>
</td> <td style="width: 52.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:num="" u4:num="" valign="bottom" width="70" nowrap="nowrap">
13<o:p></o:p>​
</td> <td style="width: 64.6pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="86">
<o:p> </o:p>​

</td> <td style="width: 102.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="136">
<o:p> </o:p>​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 21.95pt; border-width: medium 1pt 1pt; border-style: none solid solid; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext;" x:num="" u4:num="" valign="bottom" width="29" nowrap="nowrap">
6<o:p></o:p>​
</td> <td style="width: 78pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:str="Ephemeroptera " u4:str="Ephemeroptera " valign="bottom" width="104" nowrap="nowrap"> Ephemeroptera <o:p></o:p>
</td> <td style="width: 73.65pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:str="Baetidae " u4:str="Baetidae " valign="bottom" width="98" nowrap="nowrap"> Baetidae <o:p></o:p>
</td> <td style="width: 70.25pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" valign="bottom" width="94" nowrap="nowrap"> Paracloeodes<o:p></o:p>
</td> <td style="width: 52.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:num="" u4:num="" valign="bottom" width="70" nowrap="nowrap">
44<o:p></o:p>​
</td> <td style="width: 64.6pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="86">
<o:p> </o:p>​

</td> <td style="width: 102.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="136">
<o:p> </o:p>​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 21.95pt; border-width: medium 1pt 1pt; border-style: none solid solid; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext;" x:num="" u4:num="" valign="bottom" width="29" nowrap="nowrap">
7<o:p></o:p>​
</td> <td style="width: 78pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:str="Ephemeroptera " u4:str="Ephemeroptera " valign="bottom" width="104" nowrap="nowrap"> Ephemeroptera <o:p></o:p>
</td> <td style="width: 73.65pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:str="Baetidae " u4:str="Baetidae " valign="bottom" width="98" nowrap="nowrap"> Baetidae <o:p></o:p>
</td> <td style="width: 70.25pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" valign="bottom" width="94" nowrap="nowrap"> Pseudocloeon<o:p></o:p>
</td> <td style="width: 52.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:num="" u4:num="" valign="bottom" width="70" nowrap="nowrap">
65<o:p></o:p>​
</td> <td style="width: 64.6pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="86">
<o:p> </o:p>​

</td> <td style="width: 102.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="136">
<o:p> </o:p>​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 21.95pt; border-width: medium 1pt 1pt; border-style: none solid solid; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext;" x:num="" u4:num="" valign="bottom" width="29" nowrap="nowrap">
8<o:p></o:p>​
</td> <td style="width: 78pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:str="Ephemeroptera " u4:str="Ephemeroptera " valign="bottom" width="104" nowrap="nowrap"> Ephemeroptera <o:p></o:p>
</td> <td style="width: 73.65pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:str="Caenidae " u4:str="Caenidae " valign="bottom" width="98" nowrap="nowrap"> Caenidae <o:p></o:p>
</td> <td style="width: 70.25pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" valign="bottom" width="94" nowrap="nowrap"> Brachycercus<o:p></o:p>
</td> <td style="width: 52.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:num="" u4:num="" valign="bottom" width="70" nowrap="nowrap">
42<o:p></o:p>​
</td> <td style="width: 64.6pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="86">
<o:p> </o:p>​

</td> <td style="width: 102.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="136">
<o:p> </o:p>​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 21.95pt; border-width: medium 1pt 1pt; border-style: none solid solid; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext;" x:num="" u4:num="" valign="bottom" width="29" nowrap="nowrap">
9<o:p></o:p>​
</td> <td style="width: 78pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:str="Coleoptera " u4:str="Coleoptera " valign="bottom" width="104" nowrap="nowrap"> Coleoptera <o:p></o:p>
</td> <td style="width: 73.65pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:str="Elmidae " u4:str="Elmidae " valign="bottom" width="98" nowrap="nowrap"> Elmidae <o:p></o:p>
</td> <td style="width: 70.25pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" valign="bottom" width="94" nowrap="nowrap"> Macrelmis<o:p></o:p>
</td> <td style="width: 52.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:num="" u4:num="" valign="bottom" width="70" nowrap="nowrap">
100<o:p></o:p>​
</td> <td style="width: 64.6pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="86">
<o:p> </o:p>​

</td> <td style="width: 102.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="136">
<o:p> </o:p>​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 21.95pt; border-width: medium 1pt 1pt; border-style: none solid solid; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext;" x:num="" u4:num="" valign="bottom" width="29" nowrap="nowrap">
10<o:p></o:p>​
</td> <td style="width: 78pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:str="Coleoptera " u4:str="Coleoptera " valign="bottom" width="104" nowrap="nowrap"> Coleoptera <o:p></o:p>
</td> <td style="width: 73.65pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:str="Elmidae " u4:str="Elmidae " valign="bottom" width="98" nowrap="nowrap"> Elmidae <o:p></o:p>
</td> <td style="width: 70.25pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" valign="bottom" width="94" nowrap="nowrap"> Stenelmis<o:p></o:p>
</td> <td style="width: 52.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:num="" u4:num="" valign="bottom" width="70" nowrap="nowrap">
112<o:p></o:p>​
</td> <td style="width: 64.6pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="86">
<o:p> </o:p>​

</td> <td style="width: 102.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="136">
<o:p> </o:p>​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 21.95pt; border-width: medium 1pt 1pt; border-style: none solid solid; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext;" x:num="" u4:num="" valign="bottom" width="29" nowrap="nowrap">
11<o:p></o:p>​
</td> <td style="width: 78pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:str="Trichoptera " u4:str="Trichoptera " valign="bottom" width="104" nowrap="nowrap"> Trichoptera <o:p></o:p>
</td> <td style="width: 73.65pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color -moz-use-text-color windowtext;" x:str="Leptoceridae " u4:str="Leptoceridae " valign="bottom" width="98" nowrap="nowrap"> Leptoceridae <o:p></o:p>
</td> <td style="width: 70.25pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext;" valign="bottom" width="94" nowrap="nowrap"> Nectopsyche<o:p></o:p>
</td> <td style="width: 52.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; padding: 0in 5.4pt; height: 12.75pt; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color;" x:num="" u4:num="" valign="bottom" width="70" nowrap="nowrap">
20<o:p></o:p>​
</td> <td style="width: 64.6pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="86">
<o:p> </o:p>​

</td> <td style="width: 102.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="136">
<o:p> </o:p>​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 21.95pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="29" nowrap="nowrap">
12<o:p></o:p>​
</td> <td style="width: 78pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="104" nowrap="nowrap"> <o:p></o:p>
</td> <td style="width: 73.65pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="98" nowrap="nowrap"> <o:p></o:p>
</td> <td style="width: 70.25pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="94" nowrap="nowrap"> <o:p></o:p>
</td> <td style="width: 52.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="70" nowrap="nowrap"> <o:p></o:p>
</td> <td style="width: 64.6pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="86"> <o:p> </o:p>
</td> <td style="width: 102.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="136"> <o:p> </o:p>
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 21.95pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="29" nowrap="nowrap">
13<o:p></o:p>​
</td> <td style="width: 78pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="104" nowrap="nowrap"> <o:p></o:p>
</td> <td style="width: 73.65pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="98" nowrap="nowrap"> <o:p></o:p>
</td> <td style="width: 70.25pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="94" nowrap="nowrap"> <o:p></o:p>
</td> <td style="width: 52.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="70" nowrap="nowrap"> <o:p></o:p>
</td> <td style="width: 64.6pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="86"> <o:p> </o:p>
</td> <td style="width: 102.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="136"> <o:p> </o:p>
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 21.95pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="29" nowrap="nowrap">
14<o:p></o:p>​
</td> <td style="width: 78pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="104" nowrap="nowrap"> <o:p></o:p>
</td> <td style="width: 73.65pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="98" nowrap="nowrap"> <o:p></o:p>
</td> <td style="width: 70.25pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="94" nowrap="nowrap"> <o:p></o:p>
</td> <td style="width: 52.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="70" nowrap="nowrap"> <o:p></o:p>
</td> <td style="width: 64.6pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="86"> <o:p> </o:p>
</td> <td style="width: 102.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="136"> <o:p> </o:p>
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 21.95pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="29" nowrap="nowrap">
15<o:p></o:p>​
</td> <td style="width: 78pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="104" nowrap="nowrap"> <o:p></o:p>
</td> <td style="width: 73.65pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="98" nowrap="nowrap"> <o:p></o:p>
</td> <td style="width: 70.25pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="94" nowrap="nowrap"> <o:p></o:p>
</td> <td style="width: 52.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="70" nowrap="nowrap"> <o:p></o:p>
</td> <td style="width: 64.6pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="86"> <o:p> </o:p>
</td> <td style="width: 102.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="top" width="136"> <o:p> </o:p>
</td> </tr> </tbody></table>
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
Try (untested)

in F2
Code: 
=SUMPRODUCT(((A1:A1000="Ephemeroptera")+(A1:A1000="Trichoptera")),(D1:D1000))
in F3
Code: 
=SUMPRODUCT((A1:A1000="Ephemeroptera"),(D1:D1000))/SUMPRODUCT((A1:A1000="Trichoptera"),(D1:D1000))
in F4
Code: 
=SUM(D1:D1000)/SUMPRODUCT((A1:A1000="Ephemeroptera"),(D1:D1000))
 
Upvote 0
1) =SUMIF(A:A,"=Ephemeroptera",D:D) + SUMIF(A:A,"=Trichoptera",D:D)
2) =SUMIF(A:A,"=Ephemeroptera",D:D) / SUMIF(A:A,"=Trichoptera",D:D)
3) =SUMIF(A:A,"=Ephemeroptera",D:D) / SUM(D:D)

I believe you may have inverted the relationship for question 3 - if you want the percentage of Ephemeroptera, it would need to be the numerator.
 
Upvote 0
1)

=SUM(SUMIF(A:A,{"Ephemeroptera","Trichoptera"},D:D))

2)

=SUMIF(A:A,"Ephemeroptera",D:D)/SUMIF(A:A,"Trichoptera",D:D)

3)

=SUMIF(A:A,"Ephemeroptera",D:D)/SUM(D:D)
 
Upvote 0
gehusi, you are right reg. 3 - it should actually be (Sum of Eph/Sum Total)*100
If you can pl. post formula for that it would be great.

Also, I tried all your formulas, but it's either giving me Zero or #DIV/0! error code o_O
 
Upvote 0
If all the formulas failed with zeroes or #DIV/0! that would imply that your data does not match "Ephemeropta" or "Trichoptera".

Check for trailing spaces, leading spaces, CHAR(160) if the data is HTML related
TRIM(), LEN() functions will help here.
 
Upvote 0
As stated above, check your data. I tested the formulas and they work.

For number 3, the formula I gave you should do what you need it to do. Simply format the cell as a percentage if that is your desired result.
 
Upvote 0
Special-K99 and gehusi, thanks, yes there was a space in the excel cell. and the formulas work great now :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
849
gvsumich
G
E
  • Question
Adding a custom message.
Replies
1
Views
446
Micron
M
E
  • Question
VBA for Web Scraping help
Replies
0
Views
605
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,215,427
Messages
6,124,831
Members
449,190
Latest member
rscraig11

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top