Microsoft Excel - Formula for counting, when two columns agree in a row.
Asked By Ron Miller
08-Sep-10 11:17 PM
I'm wondering if there is a formula for counting something like this:
A B C D E
1 W 21 Columbus, OH 20 H
2 L 10 Columbus, OH 17 H
3 W 23 Los Angeles, CA 10 A
4 W 10 Columbus, OH 9 H
5 L 3 Iowa City, IA 10 A W for win, L for loss, H for Home, A for Away
Count when Column A is W and Column C is Columbus (or Column E is H), I know the answer is two , but hw can I set up a formula to get the answer. I could change the w to an L, also change the H to an A
Thank you,
Ron Miller
iHelper MS replied to Ron Miller
Hi Ron,
Please see if this helps.
The formula in C7 is:
=SUMPRODUCT((A1:A5="W")*(E1:E5="H"))
You can make changes to the range and values if required.
Thanks,
-iHelperMS
Goniey N replied to Ron Miller
-- You Can Use Two Formula :
=SUMPRODUCT(((A1:A5="W")*(C1:C5="Columbus, OH")));
Or
=SUMPRODUCT(((A1:A5="W")*(E1:E5="H")));
-- See Below Images :
-- First Formula....
-- Second Formula....
-- Same Result For Both Formula...
-- It Will Work 100%....
-- Help this Help You....
Ron Miller replied to iHelper MS
Ron Miller replied to Goniey N
Sumproduct Problem Excel = SUMPRODUCT(- -(M21:M5127 = "a")*(N21:N5127 = "f")*(L21:L5127 = "FALSE")) This formula does not work, but I think it should. Any suggestions? Thanks Excel Discussions Microsoft Excel (1) SUMPRODUCT (1) TEXT (1) Booleans (1) Insead (1) Are those Booleans in L21:L5127? If so you need to remove the quotes. Try one of these. . . For Boolean FALSE: = SUMPRODUCT(- -(M21:M5127 = "a"), - -(N21:N5127 = "f"), - -(L21:L5127 = FALSE)) For TEXT false: = SUMPRODUCT(- -(M21:M5127
Embed a 'match' statement in sumproduct? Excel Excel 2007 I am trying to help a coworker who wants to generate a few summary List of invalid product codes This formula works, but does not exclude the invalid products: = SUMPRODUCT(('Sheet 2'!$C$2:$C$60000 = 'Sheet 1'!$F7)*1, ('Sheet 2'!$I$2:$I I try to add a match statement, to exclude invalid products (multiply matches by zero): = SUMPRODUCT(('Sheet 2'!$C$2:$C$60000 = 'Sheet 1'!$F7)*1, ('Sheet 2'!$I$2:$I 0)*1) I cannot tell if there is a problem in my syntax, or if Sumproduct automatically thinks my match statement should be rows 2-60000 instead of 1-100, which it is not working how I'd expect. I welcome any suggestions! Thank you, Keith Excel Worksheet Discussions Microsoft Excel (1) Excel 2007 (1) SUMPRODUCT (1) ISERROR (1) MATCH (1) ISNA (1) Lifesavers (1
Filter in Microsoft Excel Excel Hi all, I am using microsoft excel 2000. I need help in filtering the data. I have two columns as below Name follows. b How we will define this filter. Please help me. . . . Thanx for reading. Robin Excel Worksheet Discussions Microsoft Excel (1) SUMPRODUCT (1) COUNTIF (1) OFFSET (1) COLUMN (1) VBA (1) À´ªàµà´¤àµ
Help with Excel Excel I have a spreadsheet with 3 columns - I want to know the number of times and col b = textb and col c = text c. how do I do this in excel? Excel Miscellaneous Discussions Microsoft Excel (1) Excel 2007 (1) Excel (1) SUMPRODUCT (1) COUNTIF (1) Bliengme (1) Times (1) Texta (1) = sumproduct(- -(a1:a10 = "texta
countif two corresponding cells meet the criteria Excel how do you countif . . . if range 1 = "A" and range 2 = "B" Excel Worksheet Discussions Microsoft Excel (1) Excel 2007 (1) Excel (1) SUMPRODUCT (1) COUNTIFS (1) Try one of these. All versions of Excel: = SUMPRODUCT(- -(A1:A10 = "A"), - -(B1:B10 = "B")) Excel 2007 only: = COUNTIFS(A1:A10, "A", B1