sumif formula

[mikeywhits]

Hi Guys,

I am trying to do the following..

If want to add the value of cells in a1:a9, if cells b1:b9 have either of the letters "R", "A", "G", AND if cells c1:c9 have the word "Open".

Thanks.

 

[VoG]

Try

=SUMPRODUCT(--(C1:C9="Open"),--ISNUMBER(MATCH(B1:B9,{"R","A","G"},0)),A1:A9)

 

[moonfish]

Are you using Excel 2007 or 2010? If so, you can use the SUMIFS formula.

 

[mikeywhits]

Hi Guys,

Vogs formula works and i can see that it does the correct calucation though it returns the value "True".

Hi Moonfish i am using excel 2007 though would prefer a solution that can be seen in excel 2003 also.

thanks,

Mike

 

[VoG]

Working here

Excel Workbook
	A	B	C	D	E	F
1	6	A	Open			13
2	5	A	Closed
3	4	R	Open
4	3	G	Open
5	2	A	Closed
Sheet5

 

[mikeywhits]

Hi guys, whilst you are here,

I am trying to combine the 3 individual formulas into 1 large one...


=IF(AND(I2<=TODAY(),P2<50%),"R","")

=IF(AND(I2<=TODAY(),P2>50%,P2<99%),"A","")

=IF(AND(I2<=TODAY(),P2>100%),"G","")

is this possible???

 

[mikeywhits]

Hi Vog,

Yes i can see from clicking into the function arguements that it gives me the right result, though for some reason the Format Result comes up as "TRUE". any ideas why??

thanks,

mike

 

[VoG]

I don't see how it could return TRUE. Make sure that the cell is formatted as General.

 

[mikeywhits]

Hi Vog,

No my bad, i must have done something wrong, but i have copied the formula over again and it works.

Thanks for your help

 

[mikeywhits]

Hi Vog,

Do you know how to combine these formulas into 1 please?

=IF(AND(I2<=TODAY(),P2<50%),"R","")

=IF(AND(I2<=TODAY(),P2>50%,P2<99%),"A","")

=IF(AND(I2<=TODAY(),P2>100%),"G","")