sumproduct help

[Bombaye]

Hi there, I am currently trying to convert this formula IF(AND(G24:G27=G24,H24:H27=H24,D24:D27=D24),"***","") into a sumproduct/sumif function with the same criteria, the asterisks and blank returns are just something I used to make sure it works. Please can someone demonstrate how to do this and which is better.
Many thanks

 

[njimack]

=if(sumproduct(--(g24:g27=g24),--(h24:h27=h24),(d24:d27=d24)),"***","")

 

[Bombaye]

Actually what I meant to say was that where the columns have the value in g24 to then sum the values in f.

If it helps I'm using excel 2007

 

[njimack]

Actually what I meant to say was that where the columns have the value in g24 to then sum the values in f.

If it helps I'm using excel 2007

=sumproduct(--(g24:g27=g24),--(h24:h27=h24),-(d24:d27=d24),--(f24:f27))

 

[barry houdini]

In Excel 2007 you can use SUMIFS for the same result, i.e.

SUMIFS(F24:F27,G24:G27,G24,H24:H27,H24,D24:D27,D24)

 

[Bombaye]

Thanks for that guys. How about if I was to add left(h24:h27),4=left(h24),4 into the formula as that doesn't seem to work?

 

[barry houdini]

You can use a wildcard in SUMIFS

=SUMIFS(F24:F27,G24:G27,G24,H24:H27,LEFT(H24,4)&"*",D24:D27,D24)