vba - Excel Array formula with IF statement -

-

i not great array formulas in excel thought maybe in community might able me. have data in 2 seperate worksheets , trying sum information given parameters. first worksheet looks 

start date   end date   division , group total 10/1/2011   10/31/2011   [need sum here] 11/1/2011   11/30/2011   [need sum here]     .            .     .            .     .            . 03/01/2015   03/31/2015  [need sum here]

the second worksheet contains information follows:

year      jan feb mar apr  ...  dec     division   group 2011       1   2   3   4   ...  21       retail      2011       5   6   7   8   ...   2       mgmt        b   .        .   .   .   .   ...   .        mw         c   .        .   .   .   .   ...   .        bb         d   .        .   .   .   .   ...   .        .          . 2015       5   5   5   5   ...   1       retail      j

the group variable has 10 different distinct values while division has 4. appreciated.

you might want sumproduct. can use match multiple cells in row , sum them up. example

=sumproduct((a1:a10=2011)*(h1:h10="retail")*(i1:i10="a")*f1:f10)

just change letters matches sheet. cell format type important. example see 2011 unquoted because stored number. if stored text need "2011". f corresponds column want total. in case october column. can add more criteria or subtract it.

you can add year dynamically off start date with

year(a1)

and make decisions based on more 1 criteria per column or or and

=sumproduct((a1:a10=year(sheet1!a1))*(h1:h10="retail")*or((i1:i10="a"),(i1:i10="b"))*f1:f10)

Comments

Post a Comment

Popular posts from this blog

Android : Making Listview full screen -

-
<linearlayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="vertical" tools:context=".mainactivity"> <edittext android:layout_margintop="5dp" android:layout_width="match_parent" android:layout_height="50dp" android:background="@drawable/location_edittext" android:id="@+id/locationeditext" android:textsize="15sp" android:textcolor="#999" android:paddingleft="15dp" /> <com.example.ravi_gupta.slider.viewpagercustomduration android:layout_width="wrap_content" android:layout_height="0dp" android:id="@+id/viewpager" android:scrollbarstyle="outsideoverlay" android:layout_weight="0.5...
Read more

javascript - Parse JSON from the body of the POST -

-
i'm using cameratag.com record video on website. service can send json response server can save data database. don't know how proceed here: the documentation states please note json repsonse passed in body of post , not form parameters. here example of how parse response in ruby: data = json.parse(request.raw_post) video_uuid = data["uuid"] but don't know how data on javascript. i'm not experienced in js please bare me :) post sample: { "uuid": "00012710-4b65-0131-ffe6-22000a499ea4", "camera_uuid": "98373a20-79ee-0130-3e42-1231390fcc11", "created_at": "2013-12-20t05:25:02.000z", "percent_complete": 100, "short_code": "kktb4q", "metadata": { }, "state": "published", "recorded_from": "http://singwho.com", "publish_type": null, "formats": [ { "name...
Read more

javascript - How to Hide Date Menu from Datepicker in yii2 -

-
basically in datetimepicker widget date choose first time, how can hide date option widget , filter according time. tried not working want hide remove button [ 'attribute'=>'time', 'label'=>'time', 'format'=>'raw', 'value'=>function($data){ return date('h:i:s',strtotime($data->time)); }, 'filter'=> datetimepicker::widget([ 'model' => $searchmodel, 'attribute' => 'time', 'clientoptions' => [ 'autoclose' => true, 'format' => 'hh:ii', 'showon' =...
Read more