Excel - Nesting countif functions

Can anyone help on how to properly write a function to count the number of
cells in a column with a certain value if they have a corresponding value in
a different column?  For example, I want to count how many times "BIG"
appears in Column B, if the same row has "B" in Column A.
A	B
1	B	BIG
2	C	BIG
3	C	SMALL
4	D	SMALL
5	B	SMALL
6	F	MEDIUM
7	F	BIG
8	E	SMALL
9	E	MEDIUM
10	D	BIG
11	C	BIG
12	B	SMALL
13	C	MEDIUM
14	E	SMALL
15	A	SMALL
16	F	MEDIUM
17	D	SMALL
18	A	BIG
19	B	SMALL
20	C	BIG

Thanks!
Evan

  Rick Rothstein \(MVP - VB\) replied...

Try this formula...

=SUMPRODUCT((A1:A100="B")*(B1:B100="BIG"))

The top end of the ranges (A100, B100) can be made to cover any span you may
have now or in the future.

Rick

or Even In the final (right most) Column I want to count the number of times that that coulmn has data in it The last row belows shows what the answer 2 ????? ????? 1 2 9 3 1 3 PLease help Excel New Users Discussions Excel (1) SUMPRODUCT (1) COUNTIF (1) COUNTA (1) RangeToCount (1) RickExcellent (1) ScoresThanks (1) ColumnsI (1) = COUNTA() - will com) Your feedback is appreciated, click YES if this post helped you. Maybe this? Odds: = SUMPRODUCT((A$1:H$1 = "Odd")*(A2:H2<> "")) Evens: = SUMPRODUCT((A$1:H$1 = "Even")*(A2:H2<> "")) Change the A thru H example columns to actual column limits you are using. - - Rick (MVP - Excel) That just counts the number of times the word even appears, let me try to rephrase my problem I have several columns need to look in Column 2, if Column (DA) C1 C2 C3 C4 Count Count evan Odd R1 Even Odd Odd neg R2 1 1 1 4 formula? R3 2 2

Return text using Sumproduct Excel Looking to return text using sumproduct: = SUMPRODUCT((C5:C9 = A1)*(D5:D9 = A2)*(E5:E9)) where E5:E9 contains text which I want to return. How do I use sumproduct to return text? It keeps giving me #Value! Thanks Excel Worksheet Discussions SUMPRODUCT (1) PRODUCT (1) LOOKUP (1) INDEX (1) MATCH (1) TEXT (1) ISNA (1) ROW (1 a clue that it can only returns numerical values, actually, only a single value per SUMPRODUCT function call; however, you can use that returned value in other formulas to do things will meet the tested for condition) perhaps concatenated together. For the first, you can use SUMPRODUCT as an argument to an INDEX function call, such as like this. . . = INDEX(E1:E9, SUMPRODUCT((C5:C9 = A1)*(D5:D9 = A2)*ROW(E5:E9))) Note the range in the first

General Sumproduct Question Excel When using Sumproduct as a multiple criteria sumif function, I've seen it used two ways: sumproduct(()*()*()) sumproduct(- -(), - -(), ()) My question is are these just two ways of doing the same thing, or does one do something the other does not? Excel Worksheet Discussions Excel (1) SUMPRODUCT (1) Horses (1) Hutch (1) ABC6E613E7DA (1) Multiplies (1) Coerces (1) MONTH (1) Essentially, both for TRUE) or 0 (for FALSE). Here is a link to a great tutorial on SumProduct. It will answer your question in mucj more detail. http: / / www.xldynamic.com / source / xld.SUMPRODUCT.html Hope this helps, Hutch The - - takes a logical value of TRUE or FALSE and the -1 positive - ie. 1. FALSE becomes 0 which is neither negative or positive. Then SUMPRODUCT multiplies the resulting values by the rest of the arguments. The format with the * will

SUMPRODUCT Excel I have a sumproduct = SUMPRODUCT(- -(ProblemClosedDate> = $D24), - -(ActionClosedDate<> " which is filtering down on the 3 defined names, all having the but auto populated, hence it is now = IF(AC812 = 1, " ", IF(AE812, AE812, " ")) If the sumproduct was = D24 then it would work but since I need it as > = it returns far to use INdirect and if so should it be in the defined name or the sumproduct, either way have you the text since i don't know the syntax? thx Excel Worksheet Discussions Microsoft Excel (1) SUMPRODUCT (1) ISNUMBER (1) COUNTA (1) OFFSET (1) CELL (1) TEXT (1) ProblemClosedDate (1) It looks crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk keywords: SUMPRODUCT description: I have a sumproduct = SUMPRODUCT(- -(ProblemClosedDate = $D24), - -(ActionClosedDate ), - -(LOB = $E24)) which is filtering down on the 3 defined names