how to find out total sale on each from the following table

[learning_grexcel]

i have one table like the following

date
1 >> 31 >> Fresh Fruit >> 30 qty
>> 5 Vegetable >> 20 qty
1 >> 20 >> mango >> 50 qty
>> 3 >> apple > 10 qty

in first column it is 1, second column it is 31 which means from 1 july to 31 july each day we will supply 30 qty
In second row, nothing in first column, but 5 in second column which means on 5th July we will supply 5 vegetables...and so on

Now I want to find out on 1st July how much total quantity sale.....similarly how much total quantity sale on second july....

 

[Marcelo Branco]

Hi,

A possible solution - not very elegant but, i think, it works
(Important: i'm assuming that exists always a comma between the numbers)

Use, say, column Z as a helper column inserting this formula in Z1
=IF(B1<>"","#"&SUBSTITUTE(SUBSTITUTE($B1," ",""),",","#")&"#","")

copy down till the end of your data

Then change the formula in G2 to
=SUMPRODUCT((($A$1:$A$300<=F2)*($B$1:$B$300>=F2)*($A$1:$A$300<>"")+($A$1:$A$300="")*ISNUMBER(SEARCH("#"&F2&"#",$Z$1:$Z$300))),$D$1:$D$300)

copy down till day 31 (or 30 accordingly with the month)

Hope that works for you

M.