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

willow1985

Active Member
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
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
=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
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
=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

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

willow1985

Active Member
That works. Thank you very much

1,084,744
Messages
5,379,584
Members
401,614
Latest member
priokatm

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...