Counting with multiple criteria

  • Thread starter Thread starter Thuy
  • Start date Start date
T

Thuy

Would be grateful if someone can help me with this

A B C D
8-9AM 9-10AM
1 CA03 ON CA03 OF
2 CA03 OF CA03 OF
3 CA03 ON CA03 TR
4 CA03 TR CA03 TR
5 CA05 OF CA04 OF
6 CA05 TR CA05 ON
7 CA04 OF CA05 TR

Note: Column A-B has the same header row, and so does C-D

I want to count in the range (A1:D7)
- How many CA03 and TR: [the result should be 3]
- How many CA03 and ON and OF: [the result should be 5]

Thanks in advance for your help.
Best regards,
Thuy
 
Assuming your table is in A1:D8,
data in rows 2 to 8 (in A2:D8)
8-9AM 9-10AM
1 CA03 ON CA03 OF
2 CA03 OF CA03 OF
3 CA03 ON CA03 TR
4 CA03 TR CA03 TR
5 CA05 OF CA04 OF
6 CA05 TR CA05 ON
7 CA04 OF CA05 TR
Click to expand...

To get > - How many CA03 and TR: [the result should be 3]

Try say, in E2:

=SUM(SUMPRODUCT((A2:A8="CA03")*(B2:B8="TR")),SUMPRODUCT((C2:C8="CA03")*(D2:D
8="TR")))

To get > - How many CA03 and ON and OF: [the result should be 5]

Try say, in F2:

=SUM(SUMPRODUCT((A2:A8="CA03")*(B2:B8="ON")),SUMPRODUCT((C2:C8="CA03")*(D2:D
8="ON")),SUMPRODUCT((A2:A8="CA03")*(B2:B8="OF")),SUMPRODUCT((C2:C8="CA03")*(
D2:D8="OF")))

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
Thuy said:
Would be grateful if someone can help me with this

A B C D
8-9AM 9-10AM
1 CA03 ON CA03 OF
2 CA03 OF CA03 OF
3 CA03 ON CA03 TR
4 CA03 TR CA03 TR
5 CA05 OF CA04 OF
6 CA05 TR CA05 ON
7 CA04 OF CA05 TR

Note: Column A-B has the same header row, and so does C-D

I want to count in the range (A1:D7)
- How many CA03 and TR: [the result should be 3]
- How many CA03 and ON and OF: [the result should be 5]

Thanks in advance for your help.
Best regards,
Thuy
Click to expand...
 
Here are two possible ways to achieve what you want:

Cells F1, F2 and F3 are used to hold the variables against which you wish to
test the data.
Put this formula in a convenient cell:
=SUMPRODUCT(((A1:A7=F1)*((B1:B7=F2)+(B1:B7=F3)))+((C1:C7=F1)*((D1:D7=F2)+(D1
:D7=F3))))

To test CA03 and TR, put CA03 in cell F1, TR in cell F2 and leave cell F3
blank.
To test CA03, ON and OF, put CA03 in cell F1, ON in cell F2 and OF in cell
F3.

The second method uses SUMPRODUCT and OFFSET:
Again, Cells F1, F2 and F3 are used to hold the variables against which you
wish to test the data.
=SUMPRODUCT((A1:D7=F1)*((OFFSET(A1:D7,0,1)=F2)+(OFFSET(A1:D7,0,1)=F3)))

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"
 
Many thanks, Andy and Max
I tried SUMPRODUCT with OFFSET and IT WORKS. It's magic!
I'd rather use this than the other option as my spreadsheet have many columns.

Thanks again,
Thuy
 
Back
Top