sumif statement

[Jackie]

I am begging for your help again ...
I would like to add another if statement to the following statement but keep
getting errors so obviously I am missing something:

=sumif(e2:e10,"1",g2:g10)and if(h2:h10,"1")

Basically what I am trying to do is sum a column g2:g10 only if both of the
other columns are 1. I will also be changing it to e is 1 & h is 2 ....
any ideas? ....

 

[Rich Mcc]

not sure if this is exactly what you want

in col G

=IF(AND(E1="1",H1="1"),"1","")

then add up the totals in col G

 

[Toppers]

Jackie,
try:

=SUMPRODUCT(--(E2:E10=1),--(H2:H10=1),--(G2:G10))

 

[Rich Mcc]

sorry i mean

=IF(AND(E1=1,H1=1),1,"")

 

[Jackie]

this is close - but it only gives me the total in g2:g10 where 1 shows up in
both e2:e10 & h2:h10 ... I was looking for it to add all the numbers in
g2:g10 that has a 1 in e2:e10 & also h2:h10:

example:
col e col h col g
1 2 20
3 1 40
6 5 60
7 8 80

I would like it to come back with a total of 60 ..

 

[Jackie]

thanks .. but I have about 30000 rows actually & I was hoping to get it to
sumif & give me 1 total without having to copy the formula down .... I can
get the sumif to work if I only give it the one critiera
(=sumif(e2:e10,1,g2:g10) but not having it lokk into 2 columns & adding all
my 1 for example ...

 

[Toppers]

Your original posting implied an AND condition not an OR:

=SUMPRODUCT(--(E2:E10=1)*--(G2:G10))+SUMPRODUCT(--(H2:H10=1)*--(G2:G10))

 

[Jackie]

I must have really been tired last night because this is EXACTLY what I need
it to do! Thanks so much for your help!