Looking to perform a criteria search

[Dom.Knight]

Hello,

On sheet1 have a database of information i input.

On sheet 3 in columns A:D it adds up the number of boxes/ total kilos for each specie from the data in sheet 1.

On sheet1 in column C it gives the presentation of each specie has "gh" or "wf".

If one specie has both gh/wf against it is there away in which on sheet 3 i can get the boxes/ total kilos for each specie to differtiate.

In my example you will see on sheet 3 total kilos for cod is 8855/ Total boxes is 253.
But my data on sheet 1 shows Cod Whole(wf) is 102 Boxes/ 3570 Kilos. Cod Gutted (gh) 151 Boxes/ 5285 Kilos.

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[Pepe Le Mokko]

Sorry, the file you talk about is absent

[Dom.Knight]

Thanks for pointing that out. I have now uploaded the file.

[bebo021999]

In my example you will see on sheet 3 total kilos for cod is 8855/ Total boxes is 253.
But my data on sheet 1 shows Cod Whole(wf) is 102 Boxes/ 3570 Kilos. Cod Gutted (gh) 151 Boxes/ 5285 Kilos.

Everything seems be OK? What is your issue?

[Dom.Knight]

Data is only entered on sheet 1. sheet 2 and 3 are reports based on data from sheet1.

On sheet 3 it takes all the data and adds up the kilos and boxes for each specie from data in sheet1.

But if each specie has both wf/ gh marked against it in sheet 1 i want to seperate it on sheet 3 to show this rather than adding up the total for all.

so for example id like sheet 3 to show:

A6 = COD, B6 = GH, C6 = 151, D6 = 5285
A7 = COD, B7 = WF, C7 = 102, D7= 3570

[Dom.Knight]

i Already have an index/ match/ count if formulae on sheet 3 in column A6. Can that be amended to give me the new answer that im looking for above.

[bebo021999]

First in sheet1, remove duplicate title below.
In A6, sheet 3:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Drag to B6
C6:

Please Login or Register  to view this content.

D6:

Please Login or Register  to view this content.

Drag all down

[Dom.Knight]

Hi thats great, however i wish to keep the duplicate title. is that possible ?

[AliGW]

Why did you change the title again? It's now not meeting forum requirements, so I will change it back. Please don't do this again.

[bebo021999]

Hi thats great, however i wish to keep the duplicate title. is that possible ?

It is so complicated. All info i the range must be extracted into the unique distinct list.

[Dom.Knight]

ok.
The orginal formulae in cell A6 on sheet 3 :-
=INDEX(sheet1!$B$7:$B$102,MATCH(0,COUNTIF($A$2:A5,sheet1!$B$7:$B$102)+(sheet1!$B$7:$B$102="Species")+(sheet1!$B$7:$B$102=0),0))

Allowed me to ignore the word "species" which appears as a sub heading every so often in column b of the data set on sheet 1 and gave me all the species that had been entered in that column.

Could something similar be written in to the new formulae posted in the above post. Just i wish to keep the sub headings at the top of each page when priting off the data on sheet 1.