Sum of text values when conditions met

[PawelK]

Hi.

I am trying to sort out a little problem and not sure if it's even possible using formulas.

I have two columns. One contains No or Yes value and the corresponding column contains L, H or M (for low,medium high).

Above them I have three cells that will give the total for each - L,M and H.
I want to use the formula to count all L, M and H whenever there is a Yes in a corresponding cell and return the total in all three cells above the data.

Thank you.

Pawel

[FDibbins]

Hi and welcome to the forum

Take a look at using =COUNTIFS(). Maybe something like...

=countifs(A1:A10,"YES",B1:B10,"L")

[PawelK]

thanks but it returns error.

[PawelK]

Thank you FDibbins and etaf.

[Tony Valko]

One way...

Data Range

	A	B	C	D	E	F
1	-----	-----	-----	-----	-----	-----
2	No	L		L	M	H
3	No	M		2	0	2
4	Yes	L
5	No	M
6	No	H
7	Yes	H
8	No	L
9	No	L
10	Yes	H
11	No	H
12	No	M
13	No	L
14	No	M
15	Yes	L

This formula entered in D3 and copied across:

=COUNTIFS($A2:$A15,"Yes",$B2:$B15,D2)

[PawelK]

Thanks Tony.

Hopefully it will work tomorrow when am in the office. Right now am using 2003 (additionally a polish version!) and realized there was no Countifs in 2003 edition. Not sure I can do it now.

[Tony Valko]

In Excel 2003 you would use the SUMPRODUCT function:

=SUMPRODUCT(--($A2:$A15="Yes"),--($B2:$B15=D2))

[PawelK]

Brilliant! Thanks very much Tony. Me 1, my boss 0
I need to brush up on my excel skills!

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

[etaf]

you could use a countif() or countifs() to do that

can you load an example spreadsheet here

[FDibbins]

You are welcome

The main reason we ask for what version you are using is to tailor suggestions based on that version. We all suggested countifS() because your profile says 2010.

2003 does not support that function, hence the error