Hello All,
First time poster.
i have a problem with a daily data dump selection where I am trying to extract information. (qty. totals, dollar averages, by company, certain times of the day, etc.)
The table looks like this example:
<TBODY>
</TBODY>
the problem i am having is that, because each row holds 2 different types of values (quantity and price) doing a normal sumifs or sum(if array becomes extremely intensive.
example below is one of my active (and working) forumula to get the total dollars for sales in a give 24 hour period (so i have to multiply adjacent cells to get the hourly dollar value:
=SUM(IF(Source!D22:D100="Sale",Source!S22:S100*Source!T22:T100))+SUM(IF(Source!D22:D100="Sale",Source!U22:U100*Source!V22:V100))+SUM(IF(Source!D22:D100="Sale",Source!W22:W100*Source!X22:X100))+SUM(IF(Source!D22:D100="Sale",Source!Y22:Y100*Source!Z22:Z100))+SUM(IF(Source!D22:D100="Sale",Source!AA22:AA100*Source!AB22:AB100))+SUM(IF(Source!D22:D100="Sale",Source!AC22:AC100*Source!AD22:AD100))+SUM(IF(Source!D22:D100="Sale",Source!AE22:AE100*Source!AF22:AF100))+SUM(IF(Source!D22:D100="Sale",Source!AG22:AG100*Source!AH22:AH100))+SUM(IF(Source!D22:D100="Sale",Source!AI22:AI100*Source!AJ22:AJ100))+SUM(IF(Source!D22:D100="Sale",Source!AK22:AK100*Source!AL22:AL100))+SUM(IF(Source!D22:D100="Sale",Source!AM22:AM100*Source!AN22:AN100))+SUM(IF(Source!D22:D100="Sale",Source!AO22:AO100*Source!AP22:AP100))+SUM(IF(Source!D22:D100="Sale",Source!AQ22:AQ100*Source!AR22:AR100))+SUM(IF(Source!D22:D100="Sale",Source!AS22:AS100*Source!AT22:AT100))+SUM(IF(Source!D22:D100="Sale",Source!AU22:AU100*Source!AV22:AV100))+SUM(IF(Source!D22:D100="Sale",Source!AW22:AW100*Source!AX22:AX100))
isnt that a beast?!?!
question, is there a more simple way to do this? I almost get a migrane just looking at it. any advice would greatly be appreciated.
~fish
First time poster.
i have a problem with a daily data dump selection where I am trying to extract information. (qty. totals, dollar averages, by company, certain times of the day, etc.)
The table looks like this example:
example | hour1 | price1 | hour2 | price2 | hour3 | price3 | ||
company1 | location 2 | purchase | 50 | 22 | 40 | 28 | ||
company2 | location 1 | sale | 10 | 40 | 50 | 42 | ||
company3 | location 2 | purchase | 40 | 22 | 30 | 24 |
<TBODY>
</TBODY>
the problem i am having is that, because each row holds 2 different types of values (quantity and price) doing a normal sumifs or sum(if array becomes extremely intensive.
example below is one of my active (and working) forumula to get the total dollars for sales in a give 24 hour period (so i have to multiply adjacent cells to get the hourly dollar value:
=SUM(IF(Source!D22:D100="Sale",Source!S22:S100*Source!T22:T100))+SUM(IF(Source!D22:D100="Sale",Source!U22:U100*Source!V22:V100))+SUM(IF(Source!D22:D100="Sale",Source!W22:W100*Source!X22:X100))+SUM(IF(Source!D22:D100="Sale",Source!Y22:Y100*Source!Z22:Z100))+SUM(IF(Source!D22:D100="Sale",Source!AA22:AA100*Source!AB22:AB100))+SUM(IF(Source!D22:D100="Sale",Source!AC22:AC100*Source!AD22:AD100))+SUM(IF(Source!D22:D100="Sale",Source!AE22:AE100*Source!AF22:AF100))+SUM(IF(Source!D22:D100="Sale",Source!AG22:AG100*Source!AH22:AH100))+SUM(IF(Source!D22:D100="Sale",Source!AI22:AI100*Source!AJ22:AJ100))+SUM(IF(Source!D22:D100="Sale",Source!AK22:AK100*Source!AL22:AL100))+SUM(IF(Source!D22:D100="Sale",Source!AM22:AM100*Source!AN22:AN100))+SUM(IF(Source!D22:D100="Sale",Source!AO22:AO100*Source!AP22:AP100))+SUM(IF(Source!D22:D100="Sale",Source!AQ22:AQ100*Source!AR22:AR100))+SUM(IF(Source!D22:D100="Sale",Source!AS22:AS100*Source!AT22:AT100))+SUM(IF(Source!D22:D100="Sale",Source!AU22:AU100*Source!AV22:AV100))+SUM(IF(Source!D22:D100="Sale",Source!AW22:AW100*Source!AX22:AX100))
isnt that a beast?!?!
question, is there a more simple way to do this? I almost get a migrane just looking at it. any advice would greatly be appreciated.
~fish