Sum with multiple criteria

moxeve

Board Regular
Joined
Jul 8, 2013
Messages
61
Hello everyone,
I have excel worksheet where is shown fuel sales with multiple criteria: Date, Fuel type, Payment method,
so i want the formula, witch can sum fuel sales with any criteria,
This is a link where you can see my excel file, and in yellow cell i want to type this formula.

https://www.dropbox.com/s/ky7qcm3hhm4mwbn/New Microsoft Excel Worksheet.xlsx?dl=0

:confused: Can someone help me.
and sorry for my english,
Hopefull, you can understand what i want.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,280
Office Version
365
Platform
Windows
If you are happy to get rid of the merged cells in row 1 & put the relevant title in each cell, you could use
=SUMPRODUCT((B1:M1=Q5)*(B2:M2=Q7)*(A3:A24>=Q6)*(A3:A24<=EOMONTH(Q6,0))*(B3:M24))
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,784
Office Version
2007
Platform
Windows
Try this

The first row must not have combined cells

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:71.29px;" /><col style="width:43.72px;" /><col style="width:60.83px;" /><col style="width:34.22px;" /><col style="width:68.44px;" /><col style="width:68.44px;" /><col style="width:68.44px;" /><col style="width:72.24px;" /><col style="width:72.24px;" /><col style="width:72.24px;" /><col style="width:43.72px;" /><col style="width:60.83px;" /><col style="width:33.27px;" /><col style="width:20.91px;" /><col style="width:20.91px;" /><col style="width:112.16px;" /><col style="width:99.8px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td><td >Q</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td rowspan="2" style="text-align:center; ">Date</td><td >Cash</td><td >Cash</td><td >Cash</td><td >Debit Card</td><td >Debit Card</td><td >Debit Card</td><td >Bonus Card</td><td >Bonus Card</td><td >Bonus Card</td><td colspan="3" style="text-align:center; ">Total</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:center; ">Diesel</td><td style="text-align:center; ">Premium</td><td style="text-align:center; ">Eco</td><td style="text-align:center; ">Diesel</td><td style="text-align:center; ">Premium</td><td style="text-align:center; ">Eco</td><td style="text-align:center; ">Diesel</td><td style="text-align:center; ">Premium</td><td style="text-align:center; ">Eco</td><td style="text-align:center; ">Diesel</td><td style="text-align:center; ">Premium</td><td style="text-align:center; ">Eco</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">23/01/2019</td><td style="text-align:right; ">200</td><td style="text-align:right; ">300</td><td style="text-align:right; ">600</td><td style="text-align:right; ">213</td><td style="text-align:right; ">228</td><td style="text-align:right; ">434</td><td style="text-align:right; ">314</td><td style="text-align:right; ">319</td><td style="text-align:right; ">204</td><td style="text-align:right; ">727</td><td style="text-align:right; ">847</td><td style="text-align:right; ">1238</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">24/01/2019</td><td style="text-align:right; ">456</td><td style="text-align:right; ">214</td><td style="text-align:right; ">351</td><td style="text-align:right; ">474</td><td style="text-align:right; ">462</td><td style="text-align:right; ">339</td><td style="text-align:right; ">386</td><td style="text-align:right; ">244</td><td style="text-align:right; ">346</td><td style="text-align:right; ">1316</td><td style="text-align:right; ">920</td><td style="text-align:right; ">1036</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">25/01/2019</td><td style="text-align:right; ">392</td><td style="text-align:right; ">492</td><td style="text-align:right; ">351</td><td style="text-align:right; ">270</td><td style="text-align:right; ">269</td><td style="text-align:right; ">473</td><td style="text-align:right; ">493</td><td style="text-align:right; ">327</td><td style="text-align:right; ">528</td><td style="text-align:right; ">1155</td><td style="text-align:right; ">1088</td><td style="text-align:right; ">1352</td><td > </td><td > </td><td >Payment method</td><td style="text-align:center; ">Debit Card</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">26/01/2019</td><td style="text-align:right; ">341</td><td style="text-align:right; ">380</td><td style="text-align:right; ">473</td><td style="text-align:right; ">551</td><td style="text-align:right; ">467</td><td style="text-align:right; ">316</td><td style="text-align:right; ">332</td><td style="text-align:right; ">393</td><td style="text-align:right; ">405</td><td style="text-align:right; ">1224</td><td style="text-align:right; ">1240</td><td style="text-align:right; ">1194</td><td > </td><td > </td><td >Date</td><td style="text-align:center; ">February-19</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">27/01/2019</td><td style="text-align:right; ">549</td><td style="text-align:right; ">318</td><td style="text-align:right; ">560</td><td style="text-align:right; ">375</td><td style="text-align:right; ">279</td><td style="text-align:right; ">488</td><td style="text-align:right; ">542</td><td style="text-align:right; ">407</td><td style="text-align:right; ">496</td><td style="text-align:right; ">1466</td><td style="text-align:right; ">1004</td><td style="text-align:right; ">1544</td><td > </td><td > </td><td >Category</td><td style="text-align:center; ">Premium</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">28/01/2019</td><td style="text-align:right; ">396</td><td style="text-align:right; ">460</td><td style="text-align:right; ">444</td><td style="text-align:right; ">579</td><td style="text-align:right; ">383</td><td style="text-align:right; ">270</td><td style="text-align:right; ">543</td><td style="text-align:right; ">303</td><td style="text-align:right; ">325</td><td style="text-align:right; ">1518</td><td style="text-align:right; ">1146</td><td style="text-align:right; ">1039</td><td > </td><td > </td><td >Sum</td><td style="background-color:#ffff00; text-align:center; ">3018</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">29/01/2019</td><td style="text-align:right; ">367</td><td style="text-align:right; ">334</td><td style="text-align:right; ">332</td><td style="text-align:right; ">340</td><td style="text-align:right; ">307</td><td style="text-align:right; ">424</td><td style="text-align:right; ">383</td><td style="text-align:right; ">224</td><td style="text-align:right; ">569</td><td style="text-align:right; ">1090</td><td style="text-align:right; ">865</td><td style="text-align:right; ">1325</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">30/01/2019</td><td style="text-align:right; ">409</td><td style="text-align:right; ">389</td><td style="text-align:right; ">295</td><td style="text-align:right; ">596</td><td style="text-align:right; ">493</td><td style="text-align:right; ">211</td><td style="text-align:right; ">443</td><td style="text-align:right; ">490</td><td style="text-align:right; ">258</td><td style="text-align:right; ">1448</td><td style="text-align:right; ">1372</td><td style="text-align:right; ">764</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">31/01/2019</td><td style="text-align:right; ">542</td><td style="text-align:right; ">270</td><td style="text-align:right; ">291</td><td style="text-align:right; ">350</td><td style="text-align:right; ">237</td><td style="text-align:right; ">474</td><td style="text-align:right; ">543</td><td style="text-align:right; ">289</td><td style="text-align:right; ">337</td><td style="text-align:right; ">1435</td><td style="text-align:right; ">796</td><td style="text-align:right; ">1102</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">01/02/2019</td><td style="text-align:right; ">368</td><td style="text-align:right; ">220</td><td style="text-align:right; ">249</td><td style="text-align:right; ">451</td><td style="background-color:#ffff00; text-align:right; ">366</td><td style="text-align:right; ">417</td><td style="text-align:right; ">389</td><td style="text-align:right; ">364</td><td style="text-align:right; ">494</td><td style="text-align:right; ">1208</td><td style="text-align:right; ">950</td><td style="text-align:right; ">1160</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">02/02/2019</td><td style="text-align:right; ">358</td><td style="text-align:right; ">316</td><td style="text-align:right; ">538</td><td style="text-align:right; ">376</td><td style="background-color:#ffff00; text-align:right; ">219</td><td style="text-align:right; ">351</td><td style="text-align:right; ">401</td><td style="text-align:right; ">361</td><td style="text-align:right; ">392</td><td style="text-align:right; ">1135</td><td style="text-align:right; ">896</td><td style="text-align:right; ">1281</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">03/02/2019</td><td style="text-align:right; ">400</td><td style="text-align:right; ">538</td><td style="text-align:right; ">353</td><td style="text-align:right; ">357</td><td style="background-color:#ffff00; text-align:right; ">311</td><td style="text-align:right; ">464</td><td style="text-align:right; ">498</td><td style="text-align:right; ">311</td><td style="text-align:right; ">476</td><td style="text-align:right; ">1255</td><td style="text-align:right; ">1160</td><td style="text-align:right; ">1293</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">04/02/2019</td><td style="text-align:right; ">495</td><td style="text-align:right; ">457</td><td style="text-align:right; ">264</td><td style="text-align:right; ">200</td><td style="background-color:#ffff00; text-align:right; ">491</td><td style="text-align:right; ">372</td><td style="text-align:right; ">524</td><td style="text-align:right; ">340</td><td style="text-align:right; ">522</td><td style="text-align:right; ">1219</td><td style="text-align:right; ">1288</td><td style="text-align:right; ">1158</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">05/02/2019</td><td style="text-align:right; ">340</td><td style="text-align:right; ">579</td><td style="text-align:right; ">469</td><td style="text-align:right; ">286</td><td style="background-color:#ffff00; text-align:right; ">303</td><td style="text-align:right; ">298</td><td style="text-align:right; ">356</td><td style="text-align:right; ">247</td><td style="text-align:right; ">558</td><td style="text-align:right; ">982</td><td style="text-align:right; ">1129</td><td style="text-align:right; ">1325</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">06/02/2019</td><td style="text-align:right; ">509</td><td style="text-align:right; ">251</td><td style="text-align:right; ">469</td><td style="text-align:right; ">428</td><td style="background-color:#ffff00; text-align:right; ">317</td><td style="text-align:right; ">502</td><td style="text-align:right; ">455</td><td style="text-align:right; ">482</td><td style="text-align:right; ">462</td><td style="text-align:right; ">1392</td><td style="text-align:right; ">1050</td><td style="text-align:right; ">1433</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">07/02/2019</td><td style="text-align:right; ">372</td><td style="text-align:right; ">467</td><td style="text-align:right; ">563</td><td style="text-align:right; ">410</td><td style="background-color:#ffff00; text-align:right; ">486</td><td style="text-align:right; ">567</td><td style="text-align:right; ">433</td><td style="text-align:right; ">582</td><td style="text-align:right; ">511</td><td style="text-align:right; ">1215</td><td style="text-align:right; ">1535</td><td style="text-align:right; ">1641</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">08/02/2019</td><td style="text-align:right; ">387</td><td style="text-align:right; ">478</td><td style="text-align:right; ">291</td><td style="text-align:right; ">486</td><td style="background-color:#ffff00; text-align:right; ">525</td><td style="text-align:right; ">371</td><td style="text-align:right; ">397</td><td style="text-align:right; ">563</td><td style="text-align:right; ">546</td><td style="text-align:right; ">1270</td><td style="text-align:right; ">1566</td><td style="text-align:right; ">1208</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">27/03/2019</td><td style="text-align:right; ">587</td><td style="text-align:right; ">386</td><td style="text-align:right; ">541</td><td style="text-align:right; ">450</td><td style="text-align:right; ">391</td><td style="text-align:right; ">342</td><td style="text-align:right; ">338</td><td style="text-align:right; ">598</td><td style="text-align:right; ">473</td><td style="text-align:right; ">1375</td><td style="text-align:right; ">1375</td><td style="text-align:right; ">1356</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="text-align:right; ">28/03/2019</td><td style="text-align:right; ">586</td><td style="text-align:right; ">309</td><td style="text-align:right; ">270</td><td style="text-align:right; ">355</td><td style="text-align:right; ">398</td><td style="text-align:right; ">444</td><td style="text-align:right; ">208</td><td style="text-align:right; ">315</td><td style="text-align:right; ">484</td><td style="text-align:right; ">1149</td><td style="text-align:right; ">1022</td><td style="text-align:right; ">1198</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="text-align:right; ">29/03/2019</td><td style="text-align:right; ">535</td><td style="text-align:right; ">467</td><td style="text-align:right; ">352</td><td style="text-align:right; ">221</td><td style="text-align:right; ">255</td><td style="text-align:right; ">253</td><td style="text-align:right; ">382</td><td style="text-align:right; ">280</td><td style="text-align:right; ">222</td><td style="text-align:right; ">1138</td><td style="text-align:right; ">1002</td><td style="text-align:right; ">827</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="text-align:right; ">30/03/2019</td><td style="text-align:right; ">446</td><td style="text-align:right; ">414</td><td style="text-align:right; ">392</td><td style="text-align:right; ">226</td><td style="text-align:right; ">330</td><td style="text-align:right; ">580</td><td style="text-align:right; ">318</td><td style="text-align:right; ">555</td><td style="text-align:right; ">558</td><td style="text-align:right; ">990</td><td style="text-align:right; ">1299</td><td style="text-align:right; ">1530</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="text-align:right; ">30/03/2019</td><td style="text-align:right; ">447</td><td style="text-align:right; ">335</td><td style="text-align:right; ">485</td><td style="text-align:right; ">557</td><td style="text-align:right; ">539</td><td style="text-align:right; ">236</td><td style="text-align:right; ">338</td><td style="text-align:right; ">541</td><td style="text-align:right; ">230</td><td style="text-align:right; ">1342</td><td style="text-align:right; ">1415</td><td style="text-align:right; ">951</td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >Q8</td><td >=SUMPRODUCT((MONTH(A3:A24)=MONTH(Q6))*(B1:J1=Q5)*(B2:J2=Q7)*(B3:J24))</td></tr></table></td></tr></table>
 

Forum statistics

Threads
1,077,917
Messages
5,337,163
Members
399,129
Latest member
ericosman

Some videos you may like

This Week's Hot Topics

Top