Excel Help

I

initroot

Senior Member
Joined
Jul 30, 2011
Messages
898
Reaction score
45
Location
Cape Town
Hi all,

I would like to summarise some information as such:

A B C
01 CAT
02 MOUSE 13
03 BIRD 4
04
05 CAT
06
07 DOG
08 CAT 3
09 DOG
10

I would like to do a function on a sheet that summarises the information above:
1. Extract the info between CAT, extract info between DOG.

It should just sum the values in C for CAT and DOG.

Should I use vba for this to create function and then just assign or is there given formula already?

Any ideas or hints I can follow up on.

Thanks!
 
Use sumif function for cat/dog values to be added

Not sure what you mean with extracting info between dog??? What info?
 
Noquarter said:
Use sumif function for cat/dog values to be added

Not sure what you mean with extracting info between dog??? What info?
Click to expand...

Hot damn, it didn't show my columns correct.

Untitled.png

Hope this clarifies the issue?

THANKS FOR HELP!!
 
StonerStuart said:
Summarize in which sense?

Elaborate, i am an excel geek!

you want to sum up?
Separately/individually sum up?

and what, column or is this your range?
Click to expand...

Okay so basicly I want to create a function that sums up the values in C for each given heading.
So I want the total for every value in between the 2 CAT headings.
Same goes for DOG.
 
Fransh said:
Okay so basicly I want to create a function that sums up the values in C for each given heading.
So I want the total for every value in between the 2 CAT headings.
Same goes for DOG.
Click to expand...

No offence but youre working the wrong way

headers should be at the top in a row.

its a little confusing looking at your sheet!
 
It's incredibly simple. The formula you're looking for (or a variation of this depending on what you need to sum) is:

=SUMIF(A1:A11;"cat";C1:C11)

To explain: A1:A11 is the range within which you are searching for a criteria. "cat" is the word you're looking for. C1:C11 is the range that needs to be summed if the criteria is matched within the search range. From there you should be able to perform all of your calculations...
 
Last edited:
DJ... said:
It's incredibly simple. The formula you're looking for (or a variation of this depending on what you need to sum) is:

=SUMIF(A1:A11;"cat";C1:C11)

To explain: A1:A11 is the range within which you are searching for a criteria. "cat" is the word you're looking for. C1:C11 is the range that needs to be summed if the criteria is matched within the search range. From there you should be able to perform all of your calculations...
Click to expand...

Thanks DJ. I might be misunderstanding your formula? SUm IF would only work if the columns in A all contain the heading,
the headings is exported into that format and preferably shouldn't alternate the exported data.

I need to identify the range between the two headings (CAT top heading and CAT bottom heading) then sum the values in that identified range.

I know this is possible by using vba function that identifies the range between two headings in column A etc.

I would like to know if there is however simpler method using predefined formulas of excel?
The same should be able with the DOG, 1. identify range between the two headings, then do sum for data in between range.

Thanks again for help so far!
 
Sorry but this is how the data is received from the exports.. To alternate the headings would be just as much work since this is 1000s lines of data.
 
Fransh said:
Thanks DJ. I might be misunderstanding your formula? SUm IF would only work if the columns in A all contain the heading,
the headings is exported into that format and preferably shouldn't alternate the exported data.

I need to identify the range between the two headings (CAT top heading and CAT bottom heading) then sum the values in that identified range.

I know this is possible by using vba function that identifies the range between two headings in column A etc.

I would like to know if there is however simpler method using predefined formulas of excel?
The same should be able with the DOG, 1. identify range between the two headings, then do sum for data in between range.

Thanks again for help so far!
Click to expand...

Oh. First thing that comes to mind is to do a separate VLOOKUP for each word that returns a unique value for each, then to use the values to define the ranges. Once the ranges are defined this way you can do a SUMIF greater than the specified unique value for each...
 
Fransh said:
Sorry but this is how the data is received from the exports.. To alternate the headings would be just as much work since this is 1000s lines of data.
Click to expand...

How can this be in any way intelligible though? I don't see the point of such a dataset in such a random order. Surely whoever is submitting the data to you can do so in a better fashion? Your alternative is to play with the pivot functions and run formulas on the pivot series to clean the data up a bit. Without actually seeing the worksheet in question it's almost impossible to decipher exactly what it is that you want, from where, and how best to do it. It currently makes no sense in the format it's in, nor why values within random ranges would be in any way intelligible...
 
I suspect that this might have to be done in VBA to count the cells in the various ranges and use these counts to define a set of cells within which to sum. I don't really know of a way, other than the VLOOKUP method I mentioned to do this...
 
DJ... said:
I suspect that this might have to be done in VBA to count the cells in the various ranges and use these counts to define a set of cells within which to sum. I don't really know of a way, other than the VLOOKUP method I mentioned to do this...
Click to expand...

Whats the value the vlookup is supposed to return?
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X