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

willow1985

Active Member
Joined
Jul 24, 2019
Messages
402
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
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,328
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
402
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
33,381
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,755
=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,084,744
Messages
5,379,584
Members
401,614
Latest member
priokatm

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top