I have a list of data, which goes something like this

Month Type Amount
Jan A 10
Jan A 15
Feb B 10
Jan B 5
March A 20

etc

Now, I have a second sheet, which I want to go something like this..

Jan

Type A TOTAL
Type B TOTAL

Feb

Type A TOTAL
Type B TOTAL

Mar

Type A TOTAL
Type B TOTAL

In order to do this I need to have a formula which says

"SUMIF (Month=Jan and Type=A)"

Is there anyway to do this?


--
grey
------------------------------------------------------------------------
grey's Profile: http://www.excelforum.com/member.php...o&userid=17951
View this thread: http://www.excelforum.com/showthread...hreadid=391795

Good afternoon Grey

There is a way to do this but we'll have to utilise arrays to do so.
The example below assumes that your data containing conditions is in
column A and B and your data to sum is in column C. My assumtion is
that your data covers 50 rows.

=SUM(IF($A$1:$A$50="January",IF($B$1:$B$50="a",$C$ 1:$C$50,0),0))

As it's an array formula don't forget to use Ctrl + Shift + Enter to
commit it (not just Enter on it's own).

HTH

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=391795

=SUMPRODUCT(--($A$2:$A$100="Jan"),--("$B$2:$B$100="A"),$C$2:$C$100)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"grey" wrote in message
...

I have a list of data, which goes something like this

Month Type Amount
Jan A 10
Jan A 15
Feb B 10
Jan B 5
March A 20

etc

Now, I have a second sheet, which I want to go something like this..

Jan

Type A TOTAL
Type B TOTAL

Feb

Type A TOTAL
Type B TOTAL

Mar

Type A TOTAL
Type B TOTAL

In order to do this I need to have a formula which says

"SUMIF (Month=Jan and Type=A)"

Is there anyway to do this?


--
grey
------------------------------------------------------------------------
grey's Profile:
http://www.excelforum.com/member.php...o&userid=17951
View this thread: http://www.excelforum.com/showthread...hreadid=391795

Yes. Use an "array" formula.

A B C

1 | Month Type Amount
2 | Jan A 10
3 | Jan A 15
4 | Feb B 10
5 | Jan B 5
6 | Mar A 20

Jan
Type A 25 The formula is: =SUM(IF((B2:B6="A")*(A2:A6="JAN"),C2:C6))
Type B 5 The formula is: =SUM(IF((B2:B6="B")*(A2:A6="JAN"),C2:C6))

This is an example of an "array" formula.
To create the array, you enter the formula as above and then
hold down the CTRL and SHIFT keys as you press the ENTER key.

There is a nice tutorial on array functions at:
http://www.cpearson.com/excel/array.htm

Regards,
Andrew


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200508/1

Of course the example I gave showed the return area on the same page as the
data, but there is no reason you can't do the same think on a different page.
The formula would just contain the sheet reference. It would look something
like:
{=SUM(IF((SheetData!B2:B6="A")*(SheetData!A2:A6="J AN"),SheetData!C2:C6))}


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200508/1

Maybe a pivot table would be faster & easier

"grey" wrote:


I have a list of data, which goes something like this

Month Type Amount
Jan A 10
Jan A 15
Feb B 10
Jan B 5
March A 20

etc

Now, I have a second sheet, which I want to go something like this..

Jan

Type A TOTAL
Type B TOTAL

Feb

Type A TOTAL
Type B TOTAL

Mar

Type A TOTAL
Type B TOTAL

In order to do this I need to have a formula which says

"SUMIF (Month=Jan and Type=A)"

Is there anyway to do this?


--
grey
------------------------------------------------------------------------
grey's Profile: http://www.excelforum.com/member.php...o&userid=17951
View this thread: http://www.excelforum.com/showthread...hreadid=391795

Hi, grey. I would probably use Subtotals instead:
http://www.officearticles.com/excel/...soft_excel.htm

But to answer your question, try the instructions he
http://www.officearticles.com/excel/...ft_excel.h tm

*******************
~Anne Troy

www.OfficeArticles.com



"grey" wrote in message
...

I have a list of data, which goes something like this

Month Type Amount
Jan A 10
Jan A 15
Feb B 10
Jan B 5
March A 20

etc

Now, I have a second sheet, which I want to go something like this..

Jan

Type A TOTAL
Type B TOTAL

Feb

Type A TOTAL
Type B TOTAL

Mar

Type A TOTAL
Type B TOTAL

In order to do this I need to have a formula which says

"SUMIF (Month=Jan and Type=A)"

Is there anyway to do this?


--
grey
------------------------------------------------------------------------
grey's Profile:
http://www.excelforum.com/member.php...o&userid=17951
View this thread: http://www.excelforum.com/showthread...hreadid=391795