VLOOKUP/ SUMIF not sufficient

[VILLABILLA]

Hello,

You can lookup data with help of VLOOKUP or SUMIF functions. You wil
have to specify lists with data in order for the formula to return th
value you made it look for. I would like to use a formula that woul
lookup the data from a whole sheet or atleast from many columns an
rows. I need this because my data is scattered all over my worksheet
and not listed.
Is there anything like that possible?

Ideas are very much appreciated!!

 

[Frank Kabel]

Hi
you may provide some more details about the structure of
your spreadsheet. There should be some kind on structure
in your scattered 'data'

 

[VILLABILLA]

Hi, Yes there is a bit of structure:

The cells i need to use as range to 'look up in' is structured lik
this (hard to explain but I'll give it a try...):

The X's resemble the data I need to look up and the Y's resemble
not-relevant data.

1 2 3 4 5 6 7 8 9 10

1 X Y X Y X Y X Y X Y

2 Y Y Y Y Y Y Y Y Y Y

3 Y Y Y Y Y Y Y Y Y Y

4 Y Y Y Y Y Y Y Y Y Y

5 Y Y Y Y Y Y Y Y Y Y

6 X Y X Y X Y X Y X Y

7 Y Y Y Y Y Y Y Y Y Y

This then counts for several sheets with much more columns and rows
all X's together would be something like 2000 X's...

I hope this makes sense to you, if not please let me know..

 

[Morgwen]

Counting multiple rows/columns

Hi,

For Example...
Data is in Rows 1-800
Data is in Colm A-CD
Data is in sheets : S2, S3, S4
You want to find # of A's, B's, C's, etc...
Results are in S1 (sheet)

Try using:
For one Workbook...
=+COUNTIF('S2'!D3:K11,'S1'!E4)+COUNTIF('S3'!D3:K10,'S1'!E4)+COUNTIF('S4'!D3:K10,'S1'!E4)

I realize this is not ideal, but, it will cover multi-sheets and.... books. Just add the additional ones to the end.

For Multi Workbooks...
=+COUNTIF('S2'!A1:H9,'S1'!B2)+COUNTIF('S3'!A1:H8,'S1'!B2)+COUNTIF('S4'!A1:H8,'S1'!B2)+(COUNTIF([Book2]B2S1!$A$1:$G$8,B2))

Hope this helps


Morgwen