# formula

#### kylefoley76

##### Well-known Member

Ok, here's what I'm trying to do. I've got the dates in column a, the type in column b. Each time type a comes up for a certain date I want to add the quantity that it appears each time on a certain day and put that number in column g. Then I want to add up the number in column d and put that in column h. Column H is really just a helper column and I don't need it, but I need it to get the number in column i which I can do myself.

Column G = the sum of column C when a or b appears in column B
Column H = the sum of column D when a or b appears in column B

The problem is that there are numbers in every cell in column c, that's why I couldn't do a sumif. I tried to do a sumif where the criteria would be column D not equal to blank, I put in <>"" as the criteria but that didn't work.

Then if I could find some way further on down to sum only a and only b.

Cell G8, G9 = the sum of column C when a appears in column B
Cell H8, H9 = the sum of column D when a appears in column B

Cell G12, G13 = the sum of column C when b appears in column B
Cell H12, H13 = the sum of column D when b appears in column B

Thanks in advance for any help

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

##### MrExcel MVP
I2:
Rich (BB code):
``````=SUMPRODUCT(--(\$A\$2:\$A\$20=F2),--ISNUMBER(MATCH(\$B\$2:\$B\$20,{"a","b"},0)),\$C\$2:\$C\$20)/
SUMPRODUCT(--(\$A\$2:\$A\$20=F2),--ISNUMBER(MATCH(\$B\$2:\$B\$20,{"a","b"},0)),\$D\$2:\$D\$20)``````

Try to adapt from this for other questions you have.

#### kylefoley76

##### Well-known Member
Rich (BB code):
``=SUMPRODUCT(--(\$A\$2:\$A\$20=F2),--ISNUMBER(MATCH(\$d\$2:\$d\$20,{"<> """},0)),\$C\$2:\$C\$20)</pre>``

The code didn't really work. At least there is no error message. I'm not sure what i'm doing wrong. I also tried the above variation, if the cell is not equal to blank. If you look at the pictures below, the answer should be .33 but instead it comes up zero. For the 2nd and 3rd picture where i tried to divide, I get an error message #div

##### MrExcel MVP
Instead of a picture, please try to post a sample we can directly map on cells in Excel.

#### kylefoley76

##### Well-known Member
Excel genie only works with PC, I have an Apple. Unless you know if excel jeanie works with apple

#### Haseeb Avarakkan

##### Well-known Member
Try,

=SUMPRODUCT(--(\$C\$2:\$C\$5000=Q2140),--(\$H\$2:\$H\$5000<>""),\$G\$2:\$G\$5000)

#### kylefoley76

##### Well-known Member
Haseeb,

Thanks, that did it

Replies
19
Views
481
Replies
3
Views
112
Replies
0
Views
268
Replies
3
Views
257
Replies
5
Views
107

1,190,631
Messages
5,982,031
Members
439,750
Latest member
megaman777

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