Excel formula help!

Doozergirl

Can anyone help me or point me in the right direction, please? I haven't done this stuff for over five years and even then it was self taught!

I would like to see the sum of 02:O90 from another worksheet only where the relevant cells in that worksheet G2:G90 say "bread" and those in H2:H90 say "butter".

(It's bread and butter for the sake of ease, IRL it's really weird company jargon.)

This is what my formula says:

=SUMIF((AND('Sheet 2'!G2:G90,"Bread"),('Sheet2'!H2:H90,"butter")),'Sheet 2'!O2:O90)

I can do it where G2:G90 says "bread" just can't add in the next criteria in the right way, I think. I know I've got my brackets and commas in the wrong places, just don't know where they should be

I have to pop out but it would be magic if someone could help by the time I get back :rotfl:

totalsolutions

www.mrexcel.com might help.

When I do it I use cut and paste and the syntax all works itself out...

Debt_Free_Chick

hhhmmmm not sure. But which cells do you want to sum? Do you want a total of the number of times that "Bread" occurs in G2:G90? If so, then you want COUNTIF.

If G2:G90 contains text, there's nothing to sum - so I guess you want to count the number of cells in which the word "Bread" appears. If so, then ..

=COUNTIF('Sheet2'!G2:G90,"Bread")

I don't know if you can extend this function to count those in G with Bread AND those in H with Butter, but you could try this ...

=COUNTIF(('Sheet2'!G2:G90,"Bread")AND('Sheet2'"H2:H90,"Butter"))

Edit: A quick google suggests ....

=COUNTIF(('Sheet2'!G2:G90,"Bread")(AND('Sheet2'"H2:H90,"Butter")))

Debt_Free_Chick

I'm gonna suggest one more thing and then bow out, disgracefully ....

=COUNT(IF('Sheet2'!G2:G90="Bread"))+(IF('Sheet2'!H2:H90="Butter"))

f1charlie

You could use the DCOUNTA function:

=DCOUNTA('Sheet2'!G2:H90,,criteria)

where criteria would be the cells containing the following:

Col G heading | Col H heading
bread | butter

Probably explained better here:

http://sysdev.uncc.edu/HowTos/Excel/Using%20DCOUNTA%20to%20Find%20Records%20that%20match%20criteria.htm

Doozergirl

Thanks DFC

I can't get either of the first two to work though It's definately the AND section that I'm not getting right then.

I do actually want the sum of a completely different column in there, but I think the idea to COUNTIF will at least show me if I've got the AND section right, then try and add the SUMIF in later.

Essentially, I'm trying to add up all the commission made by people with a certain job title(bread) in a certain area(butter).

tomstickland

Break it down into smaller elements and test them and gradually build it up to the full equation.

Are you sure that you have a proper element-by-element AND function there?

Doozergirl

f1charlie wrote: »

You could use the DCOUNTA function:

=DCOUNTA('Sheet2'!G2:H90,,criteria)

where criteria would be the cells containing the following:

Col G heading | Col H heading
bread | butter

Probably explained better here:

http://sysdev.uncc.edu/HowTos/Excel/Using%20DCOUNTA%20to%20Find%20Records%20that%20match%20criteria.htm

I've never heard of that! Will go and have a look...

Doozergirl

Suddenly inundated with replies! That's my new plan Tom, thanks.

tomstickland

I've had a look at the Help on my version of excel

SUMIF(range,criteria,sum_range)
Range is the range of cells you want evaluated.

Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, "32", ">32", "apples".

Sum_range are the actual cells to sum. The cells in sum_range are summed only if their corresponding cells in range match the criteria. If sum_range is omitted, the cells in range are summed.

That works in my simple example.

eg: SUMIF(A1 : A10,"bread",B1:B10)

That will take any case where the A column cell contains "bread" and add the B column values up.

You don't make clear in the first post what you are specifically trying to do.

tomstickland

I'm trying some methods that use the fact that TRUE = 1 and FALSE = 0 and element by element multiplication.

In the short term,I would make a new column with the equation:

D1=(A1="bread")*(B1="butter")*C1

The two logical statements in brackets will return 1 if true or 0 if false, so the D column will contain zeros except for the cases where bread and butter are true.

Then use the basic sum function on the new column.