Simple SUMIF Formula which I cant get to work

[Mickey.P]

Hi

I'm not very good with excel so please bear with me.

I'm trying to get the formula to go to a collum and if it has a 1 to go
to another collum and have a look if it has a certain month, then to go
to another collum and get the value, all on the same row.

I have tried this formula but it keeps comming back with 2.

=IF(sheet1!I:I=1,(SUMIF(sheet1!B:B,"January",sheet1!G:G)),2)

Any help most welcome.

Mickey.p

 

[Ken Wright]

Use SUMPRODUCT

=SUMPRODUCT((Rng1 = 1)*(Rng2 = "January")*(Rng3))

Ranges must all be equal length.

Don't include headers in the ranges if ranges contain numeric values.

 

[Mickey.P]

Sorry still not working

I think I did not explain what I needed.

I need a formula to got to another sheet, look at column I, if a cel
has a 1, then to look at column B , if a row has January than to go t
the column G and add the result in the relevant cell to the total.
If it was in BASIC it would be something like

IF any cells on sheet1 column I = 1 then goto column B, If any cells o
column B = January then goto same row on Column G and add to total, i
both criterias not met then disregard.

Mickey.

 

[Andy B]

Mickey

That is exactly what Ken's formula does!! When you say 'Sorry still not
working', what error/result are you getting?

Andy.

 

[Ken Wright]

Which is exactly what I gave you

For every row that has a 1 in Rng1 and January in Rng2 it will add the
corresponding values in Rng3 and then return the total of all the values in Rng3
for qualifying rows, in the cell that you have the formula.

Post the formula that you used, and clarify what format the data in Rng2 takes,
ie is it text for January or is it a real date formatted as mmmm

 

[Mickey.P]

Here is my amended version

=SUMPRODUCT((Log!I:I = 1)*(Log!B:B = "January")*(Log!G:G))

It comes up with #NUM!

Mick

 

[Mickey.P]

Range 1 if for 1 or 0
Range 2 is month "january" text
Range 3 is a pric

 

[Frank Kabel]

Hi
change to the following
=SUMPRODUCT((Log!I1:I9999 = 1)*(Log!B1:B9999 =
"January")*(Log!G1:G9999))

SUMPRODUCT don't accept a range like B:B

HTH
Frank

 

[Ken Wright]

Frank gave you the answer - You cannot use full column references in SUMPRODUCT.
The reason by the way that you don't use SUMIF is because it doesn't accept more
than one criteria. SUMPRODUCT will do exactly what you want though.

 

[Mickey.P]

Thank to all who helped.

It's working and my boss is well pleased.

Mickey.p

: