Using Countifs formula with And/Or operator (?)

[Lambrix]

I am stuck on how to make a formula that would count how many people in Section B telework on the First Monday of the pay period. The Telework data is in 4 different columns. I am stuck on using the And/Or operators. Any ideas?

Countifs(C:C,"Section B") And countifs(F:F, "Monday P1") or countifs(G:G, "Monday P1") or countifs(H:H, "Monday P1) or countifs(I:I, "Monday P1")

Any ideas?

 

[rallcorn]

Does this get it for you?
=SUMPRODUCT(--(B2:B100="Section B")*--(F2:I100="Monday P1"))

 

[pgc01]

Hi Lambrix

A question:

What happens if you have more that one match in the same row, for ex. Columns F and H. Do you want to count that row as 2 or as 1?

 

[Lambrix]

Hi Lambrix

A question:

What happens if you have more that one match in the same row, for ex. Columns F and H. Do you want to count that row as 2 or as 1?

Just as a one. But there shouldn't be any matches within the same row of information because each row represents a single employee which can't work the same telework day more than once.

Any ideas?

 

[Lambrix]

Does this get it for you?
=SUMPRODUCT(--(B2:B100="Section B")*--(F2:I100="Monday P1"))

Hi Rallcorn, thank you for your reply. Unfortunately it didn't work

 

[rallcorn]

hmmm . . . works for me. Using the following, the formula correctly counts 4 instances where column B contains "Section B" and "Monday P1" is found in the same row in columns F, G, H or I

[TABLE="width: 651"]
<TBODY>[TR]
[TD]A</SPAN>
[/TD]
[TD]B</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[TD]D</SPAN>
[/TD]
[TD]E</SPAN>
[/TD]
[TD]F</SPAN>
[/TD]
[TD]G</SPAN>
[/TD]
[TD]H</SPAN>
[/TD]
[TD]I</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Monday P1</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3</SPAN>
[/TD]
[TD]Section B</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Monday P1</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Monday P1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5</SPAN>
[/TD]
[TD]Section B</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Monday P1</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6</SPAN>
[/TD]
[TD]Section A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Monday P1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7</SPAN>
[/TD]
[TD]Section B</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Monday P1</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8</SPAN>
[/TD]
[TD]Section B</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Monday P1</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10</SPAN>
[/TD]
[TD][/TD]
[TD]4</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11</SPAN>
[/TD]
[TD][/TD]
[TD="colspan: 8"]Cell C10 formula: =SUMPRODUCT(--(B2:B100="Section B")*--(F2:I100="Monday P1"))</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[Lambrix]

Awesome! It worked! Thank you so much for helping me work through this formula!

 

[rallcorn]

Glad you got it to work.

 

[Lambrix]

Okay, so I have been trying to expand on your formula by adding one more criteria range and criteria. The Criteria range would be A2:A100 and the Criteria would be, "DC"

Here is the adjustment that I have made to the formula which is not working:
C10 formula: =SUMPRODUCT(--(B2:B100="Section B")*--(F2:I100="Monday P1")*--(A2:A100="DC"))

Any ideas?

 

[rallcorn]

Your revised formula works for me (when I added DC to some cells in Col A )

i.e. Leaving table exactly the way it was, but adding DC in A3 and A5, the formula returns 2, which is the right answer.

Hint: If you have "DC " (DC space) either in the formula or the cell, you won't have a match.

Have you ever used Formulas|Formula Auditing|Evaluate Formula to walk through a formula step by step? This is really a good way to see exactly how a formula works (and where you've gone right or wrong!) In this example, I'd narrow your formula down to rows 1-8 before stepping through it and then once you understand what the formula is doing and you have it working right, you can expand the range in your formula to be what you need.