formula

[kylefoley76]

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

 

[Aladin Akyurek]

I2:

=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]

=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

 

[Aladin Akyurek]

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

 

[kylefoley76]

Excel genie only works with PC, I have an Apple. Unless you know if excel jeanie works with apple

 

[Aladin Akyurek]

Excel genie only works with PC, I have an Apple. Unless you know if excel jeanie works with apple

Perhaps you can try...

Borders-Copy-Paste

 

[Haseeb Avarakkan]

Try,

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

 

[kylefoley76]

Haseeb,

Thanks, that did it