Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 7th 11, 08:09 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2009
Posts: 18
Default SUMIFS, one criteria range, multiple criteria

I have the following data

Column A Column B
Amount Account
100 A
50 B
20 C
200 D

I would like to sum items in column A where column B = B or C (I think
these are numbers stored as text).

I cannot get SUMIFS to work with multiple criteria but only one
criteria range even when I use the OR statement.
Can someone help me how to do this please.

thanks

  #2   Report Post  
Old September 7th 11, 01:18 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2010
Posts: 1,522
Default SUMIFS, one criteria range, multiple criteria

On Sep 7, 2:09*am, GavinS wrote:
I have the following data

Column A * * Column B
Amount * * * * * *Account
100 * * * * * * * * * *A
50 * * * * * * * * * * B
20 * * * * * * * * * * C
200 * * * * * * * * * D

I would like to sum items in column A where column B = B or C (I think
these are numbers stored as text).

I cannot get SUMIFS to work with multiple criteria but only one
criteria range even when I use the OR statement.
Can someone help me how to do this please.

thanks

Also works with previoius versions of excel
=SUMPRODUCT(--(C1:C4={"a","b"})*B1:B4)

  #3   Report Post  
Old September 7th 11, 05:40 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 829
Default SUMIFS, one criteria range, multiple criteria

"GavinS" wrote:
I would like to sum items in column A where column
B = B or C (I think these are numbers stored as text).
I cannot get SUMIFS to work with multiple criteria


=SUMIFS(A1:A100,B1:B100,"B")+SUMIFS(A1:A100,B1:B10 0,"C")

or

=SUMIF(B1:B100,"B",A1:A100)+SUMIF(B1:B100,"C",A1:A 100)

or

=SUMPRODUCT(A1:A100,--(B1:B100={"B","C"}))


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
multiply criteria in a range using sumifs dloc Excel Worksheet Functions 1 February 5th 10 08:01 PM
SUMIFS with an OR criteria Go Bucks!!![_2_] Excel Worksheet Functions 8 September 17th 09 09:30 PM
New 2007 Sumifs formula - is there a new multiple criteria vlookup westy Excel Worksheet Functions 2 July 1st 09 06:41 AM
using sumifs with multiple criteria? Celia Excel Discussion (Misc queries) 3 April 8th 09 02:14 AM
match multiple criteria ina range from multiple criteria multiplet RG Excel Worksheet Functions 8 September 28th 07 04:21 AM


All times are GMT +1. The time now is 10:45 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017