[Johnroashton]

This: SUM(IF($B$7:$B$900=$AA2,IF(OR($E$7:$E$900=$AG$1,$E$7:$E$900=$AG$3),K$7:K$900))) Is summing if it matches either condition $B$7:$B$900=$AA2 or condition (OR($E$7:$E$900=$AG$1,$E$7:$E$900=$AG$3)

I need it to sum if it matches both the bold conditions.

I've been playing around with AND(IF etc for hours and can't get anywhere. I will be extremely grateful for any help.

[JDJS]

I am not a big expert on conditionals, but IF you want to test for both conditions it doesn't look to me like your formula is doing that.

Back to the beginning, the forumla as you will know is IF(LogicalTest,ValueIfTrue,ValueIfFalse)

The AND formula, in case you don't know resolves to true only if both statements are true. So if both A1 and B1 are 1 then
AND(A1-1,B1-1)
will resolve to true (you can have more than two conditions of course)

You say you want the formula to work if BOTH conditions are true. So it looks to me that you need something like

IF(AND(A=B,C=D),SumYouWant,DoNothing?)

As an example set up a spreadsheet with values 1-5 in row 1
So A1=1, B1=2, C1-3 etc.

Then enter this formula:
=IF(AND(A1+B1=3,C1+D1=7),"YES","NO")

This will resolve to YES

Hopefully this will help you figure it out.

I sometimes find that when I am getting in a muddle it's easier to do it in steps. So I will do something like setting a value of 1 in one cell if the first thing I'm trying to establish is true. Then I use another forumla to set a value of 1 in another cell if the second condition is true. Then I use a third formula to say that if the two cells add up to 2, I do what I wanted. It is more long winded, but by chunking it up it's easier to see where you are going right and wrong, as I for one can lose the plot on these nested formulae when they get long.

[Johnroashton]

JDJS, thanks for your swift advice. I've taken some of it.

I started from scratch on a simpler document with getting the IF(AND( logic right first, which I have done.

=IF(AND($B$5:$B$16="IM",OR($C$5:$C$16=F$1,$C$5:$C$16=F$2,$C$5:$C$16=F$3)),$ D5,FALSE)

But, as you can see, now I need to do the second stage: getting Excel to count those cells which fulfil the conditions. At the moment this is done in two stages, to help you see what I'm trying to do. The first stage is that the 'Solid' value is regurgitated row by row if the conditions are met. The second stage is that those values are summed up.

I need these two stages to be in the same formula. The header rows are going to be the only safe part of the spreadsheet; the rest will be pasted over.

I attach my simplified spreadsheet so you can see what I'm doing.

[JDJS]

I'm glad you made some progress.

I'm struggling a bit to understand what you are trying to do.

You have effectively created two columns of conditional data. If what you want to do is to get a result which is the sum of the two columns, then obviously you'd just add them up into a third column.

But you don't want to do that. So to create them in one column you need to take both your column formulae and put them into a single cell and just add them together.

The problem with that approach, as I've said before, is that you end up with a horrendously long formula which is really difficult to edit, check and understand when you come back months later.

That's why I recommend chunking it up. Could you not just hide the two columns you want and show the third output column?

Alternatively have you considered another approach. I think that the lovely SUMIFS function (introduced in 2007) might just do it for you. Or have you looked at DSUM or the conditional sum wizard?

Here is the explanation of SUMIFS from the help file:
Adds the cells in a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) that meet multiple criteria. For example, if you want to sum the numbers in the range A1:A20 only if the corresponding numbers in B1:B20 are greater than zero (0) and the corresponding numbers in C1:C20 are less than 10, you can use the following formula:
=SUMIFS(A1:A20, B1:B20, ">0", C1:C20, "<10")


If that's no good, I'm afraid you are outside my knowledge range!
Good luck.

[The Villan]

I have created a pivot table based on your data.

Is the Pivot table the sort of report you are looking for?

[Johnroashton]

What I'm trying to do is have a =SUMIFS(AND(a1=b1),(OR(a2=b2,a2=c2,a2=d2)),a3:z3) sort of argument. I.E. I need condition 1 to be true, and one of condition 2, 3 and 4 to be true, with the result that it sums a column of numbers for which those criteria are true.

The Villian, thanks, I hadn't thought of pivoting it, but that looks like it could work with the rest of my spreadsheet.

I don't understand! It should be so easy to do SUMIFS(AND(...),(OR(...,...,...)),...) ! It feels like I'm just putting a bracket wrong somewhere or something.

If anyone manages to build a successful formula using the spreadsheet I attached in my previous post, I would be very grateful.

Just to clarify, it needs to say something like this:

SUM values where the following are true
-The brand is IM
-Any of the following:
--The region is North
--The region is Scotland
--The region is Ireland

What I've come up with, and it doesn't work, is:
=SUMIFS(D516,$B$5:$B$16,"IM",OR($C$5:$C$16=F$1,$C$5:$C$16=F$2,$C$5:$C$16= F$3)))

[OBP]

John, have considered doing this in Access, it is fairly straightforward using Queries.
I can't see what is wrong with your Formula, it is working on the copy that you posted, the only change that I would make is to replace the False with a 0, so that the Column can then be summed up if required.

[The Villan]

I also did this SUMIFS spreadsheet

Column F to G has all the summary of the data in Columns A to D

There is a function in Excel 2007 called SUMIFS and thats what I used.

Are you using 2007?

You can always buy the Microsoft Office 2007 for £35 from here http://www.software4students.co.uk/

[Johnroashton]

Thanks, all, I suppose that I can always do it in 2 steps like you suggest, and there's no real reason except neatness why I need to do it all in one cell.

I suppose it's one of those things where you just feel like it should be easy!

I'm afraid buying new software is out of the question

Thanks for helping and if anyone does find a way to do this operation in just one cell, I'll be really grateful.

[The Villan]

Well this is how you do it with just one formula for each group