# Requesting Array simplification if possible

#### Micfish

##### New Member
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:
 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:

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

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### pgc01

##### MrExcel MVP
Hi
Welcome to the board

Not sure I understood exactly, but for ex. to get the total of Sale or Purchase, try in C9:

=SUM(IF(\$C\$2:\$C\$4=B9,IF(ISEVEN(COLUMN(\$D\$2:\$H\$4)-COLUMN(\$D\$2)),\$D\$2:\$H\$4*\$E\$2:\$I\$4)))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER and not just ENTER.

Copy down

#### Micfish

##### New Member
Ok PGC, you make that look so elegant. thanks for your help.

#### pgc01

##### MrExcel MVP
You're welcome. Thanks for the feedback.

#### Micfish

##### New Member
PGC, if i may ask.... just so I better understand the funtion logic,

=SUM(IF(\$C\$2:\$C\$4=B9,IF(ISEVEN(COLUMN(\$D\$2:\$H\$4)-COLUMN(\$D\$2)),\$D\$2:\$H\$4*\$E\$2:\$I\$4)))
sum(if estabilshes the array =b9 sets the criteria, iseven returns a true if even and false if odd, the column establishes the range, what does the -column\$d\$2 do?

#### pgc01

##### MrExcel MVP
=SUM(IF(\$C\$2:\$C\$4=B9,IF(ISEVEN(COLUMN(\$D\$2:\$H\$4)-COLUMN(\$D\$2)),\$D\$2:\$H\$4*\$E\$2:\$I\$4)))
sum(if estabilshes the array =b9 sets the criteria, iseven returns a true if even and false if odd, the column establishes the range, what does the -column\$d\$2 do?

Hi

You subtract column\$d\$2 so that you always get the distance (in number of columns) to the first column (D)

COLUMN(\$D\$2:\$H\$4)-COLUMN(\$D\$2)

for COLUMN(\$D\$2:\$H\$4) you get the array: {4,5,6,7,8}
for COLUMN(\$D\$2) you get the array: {4}

when you subtract you get the array: {0,1,2,3,4}

this allows you to test with ISEVEN() and only consider columns 0,2,4 ..., that are those you are interested in.

1,191,120
Messages
5,984,762
Members
439,909
Latest member
daigoku

### 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.

### Which adblocker are you using?

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

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