Looking for a more elegant way to write the following countifs formula

willow1985

Active Member
Joined
Jul 24, 2019
Messages
432
Office Version
2019
Platform
Windows
Hello,

was wondering if anyone could help me with the below formula. Is there a more elegant way of writing it rather than adding each additional section?

I want the formula to count E:E if it matches Z2, and if S:S matches B12, C12 and D12. Would this be some sort of Countifs/And formula?

=COUNTIFS('WO Report'!$E:$E,Z2,'WO Report'!$S:$S,$B$12)+COUNTIFS('WO Report'!$E:$E,Z2,'WO Report'!$S:$S,$C$12)+COUNTIFS('WO Report'!$E:$E,Z2,'WO Report'!$S:$S,$D$12)

Thank you for all of your help :)

Carla
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,341
Not much more elegant, think you got it about as good as its gonna get. This is pretty much the same

=SUMPRODUCT(('WO Report'!E:E=Z2)*(('WO Report'!S:S=B12)+('WO Report'!S:S=C12)+('WO Report'!S:S=D12)))
 

willow1985

Active Member
Joined
Jul 24, 2019
Messages
432
Office Version
2019
Platform
Windows
=COUNTIFS('WO Report'!$E:$E,Z2,'WO Report'!$S:$S,$B$12,'WO Report'!$S:$S,$C$12,'WO Report'!$S:$S,$D$12)

This formula does not work but is there a reason why?

the reason I ask is that this formula works in another workbook I have and I thought the conditions were the same.

The other workbook where the formula works:

=COUNTIFS(Report!$M:$M,A7,Report!$A:$A,'Weekly Metrics'!$B$4,Report!$P:$P,'Weekly Metrics'!$D$4)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,626
Office Version
365
Platform
Windows
The countifs function works on an AND basis, so you are looking for any cell in col S that matches B12 & C12 & D12.
So unless B12,C12 & D12 are all the same value, the formula will give you 0.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,769
=COUNTIFS('WO Report'!$E:$E,Z2,'WO Report'!$S:$S,$B$12,'WO Report'!$S:$S,$C$12,'WO Report'!$S:$S,$D$12)

This formula does not work but is there a reason why?
What you are saying is that you want

'WO Report'!$S:$S

to be equal to $B$12 AND to be equal to $C$12 AND to be equal to $D$12

and I think you want OR, not AND

Try instead:

=SUMPRODUCT(COUNTIFS('WO Report'!$E:$E,Z2,'WO Report'!$S:$S,$B$12:$D$12))
 

Forum statistics

Threads
1,089,488
Messages
5,408,560
Members
403,215
Latest member
DblDocWhitaker

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top