Exclude certain cells in a row not to be included in a function.

[Dom.Knight]

Hello,

Im not sure what exactly I'm looking for this time.

From the data base of information I have I wish for each "specie" which are in Column B to add together the number of "boxes" in Column E and the "total kg" in column J and appear
in Cells M7, N7 & O7.

I only want the totals for each species to Appear rather than that of each size. But id like this information to appear automatically rather than having to perform a search for each specie.

Hopefully that makes sense.

Dom

[PaulM100]

Try:
M7: =IFERROR(LOOKUP(2,1/(COUNTIF($M$6:M6,$B$7:$B$101)=0),$B$7:$B$101),"")
N7 =SUMIF($B:$B,M7,$E:$E)
O7: =SUMIF($B:$B,M7,$J:$J)

[Dom.Knight]

Thanks for the answer, is there a way in which I could exclude Cell B47+B48+B9+B50 that contains the word specie from the results in the criteria.

[JeteMc]

Two options:
1. Select cell A7 > view tab > freeze panes > freeze panes (1st option)
Then delete cells A47:J50 and A93:J96
2. Use the table produced by PaulM100's formulas as the calculation engine and then in three other columns (Q:S in attached file) use:

Please Login or Register  to view this content.

Let us know if you have any questions.

[boopathiraja]

Hi,

M2=

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.

[Dom.Knight]

Hello, is there away in which i can adjust the formulae in the above post so that any specie in column B which has GH OR WF in column C appears as a seperate specie in the report in columns M, N and O.

For example cod GH, Then cod WF

[JeteMc]

One way would be to add a column (L in the attached file) to the data which would conditionally concatenate columns B and C using: =IF(OR(C7="GH",C7="WF"),B7&" "&C7,B7)
The formula for column M becomes*: =IFERROR(INDEX($L$7:$L$102,MATCH(0,COUNTIF($M$3:M6,$L$7:$L$102)+($L$7:$L$102="Species")+($L$7:$L$102=0),0)),"")
The formula for column N becomes: =IF(M7="","",SUMIF($L:$L,M7,$E:$E))
The formula for column O becomes: =IF(M7="","",SUMIF($L:$L,M7,$J:$J))
*Denotes an array entered formula (see post #5)
Note that the added column may be moved and/or hidden for aesthetic purposes.
Let us know if you have any questions.