Wildcarding cell references

[BritishBuddha]

I want a cell to be able to check each cell on row 1 using IF and if a specific value is present to COUNT/COUNTIF all cells from *3:*151 (where * denoted the coloumn of the cell checked)

Is wildcarding cell coloumns/rows possible?
I hope I've explained this correctly.

Thanks

 

[GlennUK]

That first link is broken, so can't see what you're on about.

 

[GlennUK]

I can see your links now, but it doesn't make things any clearer. Can you explain the logic behind what you want to happen, in precise terms?

 

[BritishBuddha]

In this screenshot: http://i52.tinypic.com/4ptbfl.jpg
Row two contains the weather on different dates,
A3 to A151 contains vehicle numbers,
B3:IV151 contain information on faults.

I'm trying to count how many times a fault occurred during specific types of weather for each vehicle. I want excel to cross reference all days that are 'WET' with all faults that contain the term 'DBF' and count them.

 

[GlennUK]

You previously said

B1:IV1 will contain one or two words

... explain how the logic would operate for 2 words.

 

[BritishBuddha]

The two words would be 'slight rain' so it would be treated the same as 'wet'. If this was a problem, rewording would be simple enough.

The last two weather conditions would be 'frosty' and 'dry'.

 

[GlennUK]

And the cross-referencing .. WET with DBF ... your screenshot shows also:
DOORS
EB
FC4
FEC
FTD
NCT

Are these to be searched for simultaneously? Or what?

 

[BritishBuddha]

No, I would be happy if I could get it to just check the 'wet' and 'dbf' for each single vehicle row. Then I could just swap other faults and weather conditions as needed.

 

[GlennUK]

Here's a starting point:

=SUMPRODUCT(--(1:1="First check")*--(NOT( ISERROR(FIND("Second check",3:151)))))

.... like my first formula but operates on embedded text within the contents of cells, rather than whole cell contents.

 

[BritishBuddha]

It works perfectly I could kiss ya!

This should save me quite a bit of time, thanks for your patience.

BB

 

[GlennUK]

That's great!