sumif but additionally there maybe filtering

Hi,

Column A | Column B | Column C
....            | 100         | $
....            |   50         | €
....            |   60         | $

=sumif(C1:C3;"$";B1:B3)
No problem until this point.

But, when I appply some filtration in column A, I need the sum to change.
When I write the formula as above, the sum does not change after filtration.

Thanks and regards,

Cousin Excel
0
Utf
1/20/2010 2:53:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
363 Views

Similar Articles

[PageSpeed] 37

Hi,

Try this

=SUMPRODUCT(--(C1:C10="$"),SUBTOTAL(9,OFFSET(B1:B10,ROW(B1:B10)-MIN(ROW(B1:B10)),0,1)))


-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"CousinExcel" wrote:

> Hi,
> 
> Column A | Column B | Column C
> ...            | 100         | $
> ...            |   50         | €
> ...            |   60         | $
> 
> =sumif(C1:C3;"$";B1:B3)
> No problem until this point.
> 
> But, when I appply some filtration in column A, I need the sum to change.
> When I write the formula as above, the sum does not change after filtration.
> 
> Thanks and regards,
> 
> Cousin Excel
0
Utf
1/20/2010 3:06:05 PM
Reply:

Similar Artilces:

need a rule to filter attechment of 1byte
hola: my problem is easy, a need to create a rule to filter all emails that comes w/ attachment of 1kb. Easy Any help thanks ivan ...

Additional POS Product key
Help, my Microsoft partner sent me a new Product key for an additional lane in RMS, but failed to send me any instructions on how to install it. I would like to know how to set up the additional lane. This is a multi-part message in MIME format. ------=_NextPart_000_056E_01C78CFF.E9434EC0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Knightsbridge, Interested in why you call him a partner if he doesn't help you install = it. How did the other stations get installed? Read through the help file in SO Administrator or the first 50-...

Sumif/SumProduct/lookup question
I want to insert subtotals only for items containing at least two specified costs. A B C 1 Item Name Cost Title Cost Value 2 Item 1 Cost 1 5 3 Item 1 Cost 2 3 4 Item 1 Cost 3 4 5 Item 1 Cost 4 1 6 Item 2 Cost 1 9 7 Item 3 Cost 3 6 This setup continues for 28,000 rows. I want to separately sum all the cost values for each item number which contains both a cost 1 and cost 2. Are conditional subtotals possible, so that onl...

Advanced Filter vs Excel Errors
Excel 2000 ... Often times when attempting to use "Advanced Filter" ... I get error message that ... Excel has generated errors & will be closed ... :( Anybody know what gives??? Very frustrating ... Am I missing something? ... Thanks ... Kha ...

how to filter dates?
I am trying to filter out a number of date groups from a large data base. I have not had much luck using advanced filter with wildcards. how do I go about filtering aout certain months from a large range of years? One way would need a helper column with the formula - =month(A1) This will return the month number from the target cell and sorting on the helper column will bring all the entries together by month. Regards. Bill Ridgeway Computer Solutions "abigail" <abigail@discussions.microsoft.com> wrote in message news:69390A39-14DB-4D7B-B33C-79ABE7ED01E0@microsoft....

sumif
I have a formula: =SUMIF('C:\Users\John\Documents\Miscellaneous Stuff\[dbl_approved_items.xlsx]dbl_approved_items'!A:A,C401, 'C:\Users\John\Documents\Miscellaneous Stuff\[dbl_approved_items.xlsx]dbl_approved_items'!B:B) in a file called product.xlsx. But, the cell only works when I have dbl_approved_items.xlsx OPEN. If only the product.xlsx file is open, then I get #VALUE! in the cell. Why? I don't want to have to open two files in order to get my formula to work. Do I have to use VLOOKUP instead? -- There are some functions that only wo...

Question: Relative CPU Usage of SUMIFS & SUMPRODUCT
I'm building an Excel model that will summarize data from a table (18,000 rows x 65 columns), and I want to minimize the number of calculations / CPU utilization. So my question is: Is it more efficient (CPU utilization) to use SUMIFS or SUMPRODUCT? Thanks, -- Matthew >Is it more efficient (CPU utilization) to use >SUMIFS or SUMPRODUCT? Use SUMIFS -- Biff Microsoft Excel MVP "Matthew" <anonymousnews@gmail-dot.com> wrote in message news:028DA955-B4FF-4474-967D-FC64E1DF28AB@microsoft.com... > I'm building an Excel model that will summarize data fr...

SUMIF and MULTIPLE DATA
I have a spreadsheet with about 6 columns and 25 rows of data. One of the columns represents dollar amounts...and the remaining columns represent account names, account numbers...etc. I'm trying to come up with a way to sum all the dollar amounts if the data in the other columns is the same. I've used the sumif formula when I have only one piece of data I'm looking for in common, but how can I get the same result when I'm looking for information with at least two columns of the same data? I've tried used the sumproduct formula. =SUMPRODUCT(($B$106:$B$505="*042000...

junk email filter
Hi all, In order to use junk email filter, do we have to use outlook cache mode? Years ago, we turned on cache mode but some users complain about receiving new mails timely. Later, we turned off cache mode. Exchange 2003 Sp2/outlook2003/outlook 2007 Thank you. On Sat, 19 Dec 2009 09:59:01 -0800, ed <ed@discussions.microsoft.com> wrote: >In order to use junk email filter, do we have to use outlook cache mode? Which junk mail filter -- the one provided by Outlook or the one provided by Exchange? >Years ago, we turned on cache mode but some users complain a...

Using the Median function with Auto-filter
How do I get median (=median(A1:A50000) to show a result for only filtered cells? When I autofilter on a different column and the number of visible cells is reduced by the filter, the median stays the same as the unfiltered list. Thanks! Mark, You could use a column of helper cells. In B1 enter =IF(SUBTOTAL(3,A1)=0,"",A1) and copy down to match column A. Then array enter the formula (enter using Ctrl-Shift-Enter) =MEDIAN(IF(B1:B50000<>"",B1:B50000)) Filtering column A will change the result of the SUBTOTAL functions, and thus the res...

Service Fee will not recognize additional revenue
I've got a service fee set to earn "when performed". The fee had previously reached 100% earning and then we had an extension. GP PA allowed me to add an amount, which was then billed, but now it will not recognize the associated revenue. The project recognizes the additional amount in the Project Amount but will not earn. Is there anyone who has encountered this before or understands what GP is thinking? Any suggestions on what I can do to work around this? Thanks for any suggestions. ...

Excel Sum with Filter & Copy specific Data
Hello, I have two questions regarding Microsoft excel. If you could help me I'd really appreciate it! QUESTION #1 I have 2 worksheets. One has a list of ID numbers (unique - one of each) and a column for totals (which is blank as of now) WORKSHEET1 A B ID #: Total: 1 2 3 4 5 The other has a list of ID numbers (many listed multiple times), has a number entry which I need to be summed and a date (which I filter by to get a range) WORKSHEET2 A B C 1 345 Jan 1 2 327 Feb...

Filtering based on a value in middle of a word
Hi, Please tell me if a column can be filtered based on the value in the middle of a word. For eg. Col A ****_M_**** ****_M_**** ****_M_**** ****_M_**** ****_M_**** ****_B_**** ****_B_**** ****_B_**** ****_B_**** ****_B_**** Now i want to filter this column so it show the cells with ****_M_**** in it. If the filtering is not possible then, please let me know any other way. Thanks in advance. Regards, Navin Data>Filter>Autofilter>Custom Use the "Contains" parameter. Gord Dibben MS Excel MVP On 5 Jan 2007 12:24:45 -0800, "navin" <navin.narayana@gmail...

Problem with DoCmd.Open Form code
Hi part of this was already answered a while ago but I have now found a problem with it that I need to fix, im just unsure what to do. I have a main table that I wanted to be set up so that when using navigation buttons you couldnt click into a new blank record. This was fixed by turning off allow additions to the forms properties. I also needed to be able to add a new record but via a click of a button on another form and for the on click action used..... DoCmd.OpenForm "Jobs", , , , acFormAdd The only problem I have is that once I the form opens up I can save the record and g...

Filtering out email for which no SMTP name exists
Hi All, I get a large number of emails directed at my domain everyday to SMTP accounts that don't exist... joeblow@domain.com, thisdoesntpossiblyexist@domain.com, lkjadgakeid@domain.com etc etc Is there a simple way for me to send or forward emails to SMTP addresses that don't exist in my domain off into oblivion, so I don't have to deal with them? Right now, all "Undeliverable" emails are being forwarded into MY email Inbox, and I delete them. (I use a Rule to move them to another folder, but, still...there must be a better way, isn't there?) Or is there ...

Filter out records
I have thousands of records in excel where some records are duplicated with one field. How can I retrieve the duplicate field record in another worksheet?? Million thanks. I don't quite understand your question, but Chip Pearson has a bunch of techniques for working with duplicates at: http://www.cpearson.com/excel/duplicat.htm Grey wrote: > > I have thousands of records in excel where some records are duplicated with > one field. How can I retrieve the duplicate field record in another > worksheet?? > > Million thanks. -- Dave Peterson ec35720@msn.com ...

FS filter driver post create
I am trying to scan files in user land when a file write happens to a USB drive. My FS filter is attached to the drive I am interested in. When I copy a file to the drive and capture the windbg log, I see that for every copy, post cretae() is invoked multiple times. Why is this the case? ...

Filter Current Month Records in Report
I have a report that needs to display the total records of current Month (table have all year records)from Table name "Audits", the field cointaining the date records is "Date". How can I write an expression in a textbox to show this tota in the report?l, or how can I do this in VBA (recordset). You should not name your date field 'Date' as it is a reserved word and may cause problems. To your request - create a calculated field like this --- Year_Mon: Format([YourDateField], "yyyymm") Then use this as criteria for current month --- Format(Da...

What is Anti-Spam Filter.(thunderbird spam filter)
thunderbird spam filter, Spam is unsolicited and unwanted email. Other common names of spam are spam mail, bulk email or just junk mail. Today, spam has reached such monumental proportions that almost every other message we receive is spam. Protecting the inbox with anti-spam filter has become one of the barest necessities for almost all PC users. In a few words, anti-spam software helps detect spam. It is a program that detects unsolicited and unwanted e-mails, and doesn't let them slipping into your inbox. The anti-spam program is installed onto your computer and filters your inbound e...

Search Folder for additional Exchange account
Hi In my profile I have set to open an additional Exchange account. In OL2003 I could have a second search folder to show me unread email from this account. When trying to creating a new search folder I am only offered to set it up for the main account and for the archive folder. How can I create a search folder for the additional account in OL2007? Thanks Franz If opened as an additional mailbox, you would need to set the search folder options under that mailbox. Search folders do not work across mailbox stores. -- Milly Staples [MVP - Outlook] Post all replies to the group to ke...

maybe a Macro?
I have use Excel in a very limited capacity for years so I am going to try to explain what I want the best I can. I have a spreadsheet that I created to keep track of the services and inspections of company vehicles. This works great but every week I have to go through each unit to high light the units that need attention. The spreadsheet looks like the following: Unit (B1), Last Pm Date (D1), Last PM Miles (E1), Last DOT Date (F1),Current Miles (G1), Next Pm Date (I1), Next Pm Mileage (J1), Next DOT date (K1) What I do now is every week go through and change the text red on the areas...

Will SUMIF --ever-- work with closed workbook references?
Some of my co-workers believe beyond a reasonable doubt that they have seen formulas in their workbooks using a SUMIF work on a closed workbook, ie no #values. Is this ever possible? The only conclusion I can make is that the file's workbook option of saving external reference values was enabled, and the file was saved when all the related workbooks were open and values were actually in the cells. Is my hypothesis correct? Or, is SUMIF not so black and white? Will any function (SUMIF, COUNTIF, OFFSET, etc.) requiring a range EVER work on a closed workbook, which creates an array type ref...

Inserting Filtered RC cell information into other worksheets
Unsing 2003 Created a macro to add then copy/past cell info from one worksheet to a series of other new worksheets. Works fine. The reason for the macro was to automate the process of adding a worksheet (which is limited to the 255 character limit) then copy/paste cells so as to overcome the 255/per cell limitation. Now I have a new series of worksheets "A 1 thru 10". I would like to populate the cells of the new worksheets with certain cells existing on another worksheet, Named "B", which has filtered data. Thus worksheet A1 has 10 cells (in a different layout on...

Advanced filtering in Excel to find records ending in xxx?
I checked the Help area and got lots of good information about using advanced filters in Excel. The question I still have is: How do you filter on a text field to find words ending in a letter or combination of letters? For example, I want a list of the records where a particular text field ends in the letters "en". I tried using "*en", but that also gives you words where the "en" appears in the middle of the word. try this in autofilter click custom equals *en this is wildcard find topmost cell must be column head. -------------- dtencza <dten...

Can I copy sent message to additional sent folder(s).
Using WLM 14.0.8089.0726, after I send a e-mail message, a copy of it gets sent to the "Sent Folder". Can I have the same message sent to an additional folder? If that is possible, how could it be done? I would like to have Sent e-mail messages, saved in two locations. Checked Rules, and did not find anything that sounded like it would work. Thanks. No, message rules only work on incoming messages. You will have to copy the Sent messages manually. --=20 Gary VanderMolen, Microsoft MVP (Mail) Microsoft MVP program: http://mvp.support.microsoft.com "Fishe...