countifs

Mattlake

Board Regular
Joined
Apr 9, 2020
Messages
91
Office Version
  1. 2021
Platform
  1. Windows
Hi

I hope someone is going to have a simple answer for a countifs query i have need resolving.

I have a long spreadsheet (1400 lines) which someone has 'been using' badly, and i need to count if certain words are used in a column.

i need to count light fittings unfortunately the person who entered it all in put model numbers (all in column E) in that no longer exist so i need to count them if the field has 600x600 and 40w as well as a having an x in column H

I have got =COUNTIFS(Sheet1!E:E,B3,Sheet1!H:H,"X") (B3 has the text of 600x600 1 x 40w EBMCFP140Z) to work, but with this would need copy every different entry in to a single column

does this make sense and if so (phew) is there a way?

Thank you for saving the headache

Regards
Matthew
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Try putting this into B3 600x600*40w* and use your existing formula
 
Last edited:
Upvote 0
Solution
try this

Book1
ABCDEFGHIJ
1
2
3600x600 1 x 40wX4
4
5
6
7
8
9
10
11600x600 1 x 40wX
12600x600 1 x 40wX
13600x600 1 x 30w
14600x600 1 x 40w
15
16
17
18
19
20
21
22
23
24500x600 1 x 40wX
25600x600 1 x 40wX
26600x600 1 x 30wX
27600x600 1 x 50wX
28
29
Sheet1
Cell Formulas
RangeFormula
J3J3=SUMPRODUCT(--(ISNUMBER(SEARCH("600x600",E1:E27))),--(ISNUMBER(SEARCH("40w",E1:E27))),--(H1:H27="X"))
 
Upvote 0
MrExcelPlayground20.xlsx
BCDEFGH
1600x6002ThingOther
240w600x600 jamie 40wx
3600x600 fredx
4600x600 40w harry
540w jamiex
640w John 600x600x
Sheet16
Cell Formulas
RangeFormula
C1C1=SUMPRODUCT(--ISNUMBER(SEARCH(B1,E:E)),--ISNUMBER(SEARCH(B2,E:E)),--(H:H="x"))
 
Upvote 0
Thanks for this
I have used the sum =COUNTIFS(Sheet1!E:E,B3,Sheet1!H:H,"X") which works
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
Thanks for this
I have used the sum =COUNTIFS(Sheet1!E:E,B3,Sheet1!H:H,"X") and entered 600x600*40w* into B3 which works (sorry forgot to put the bit in that was edited)
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top