Formulas

[Guest]

I am trying to write something like this:

If the date in column a = jan and the 1st letter of column h6 is a, add
h6&h7 together, if not 0

 

[Guest]

I assume that "jan" in your example if in row 6 of column a (otherwise you'll
need to make the necessary adjustment).

=if(and(month(a6)=1,left(h6,1)="a"),h6+h7,0)

Note that the date in a6 needs to be a date/date serial #, not text.

Will

 

[Guest]

That's not working and I don't know why? Any other ideas?

 

[Pete_UK]

If h6 begins with the letter a then it must be text, so you will have
to change the + sign to & (in which case you probably also want "0"
instead of just 0), like so:

=if(and(month(a6)=1,left(h6,1)="a"),h6&h7,"0")

Hope this helps.

Pete

 

[Guest]

Hi Pete,

One more problem, is there any way to make the h6 a range, such as H6:H25, I
tried that, but it didn't work. In other words, If Column A is Feb and
column H6 through H25 contains an "A", sum AX6 through AX25? I can't seem to
get it to work. Any help at all would be greatly appreciated.

Thanks,

D

 

[Pete_UK]

Try this:

=SUMPRODUCT((MONTH(A6:A25)=2)*(LEFT(H6:H25,1)="A")*(AX6:AX25))

Assumes proper dates in column A and column H begins with "A".

Hope this helps.

Pete

 

[Guest]

Thank you, Thank you, Thank you, you have just saved me so many hours of
calculations and errors.

 

[Guest]

Thank you, thank you, thank you, you have just saved me so many hours of
calculation work and errors.

 

[Pete_UK]

So good you had to say it twice !! <bg> Thanks for feeding back.

Pete

 

[Guest]

One more question? Is there a way to make this equation look at ranges??