Sumif with an array for criteria
Hello, I am trying to write a sum if array formula that will look up against
multiple criteria and then finally compare to an array...here is an example
of what I wrote:
The formula works fine if I take out the $H$18, but as soon as I try to
match that, it will not work...
So to clarify what I am trying to do:
Sheet 2 contains my data:
Column A is weeks -
Column K is Sales - (what I want to sum)
Column C is SKU ID
Sheet 3 is the Form I am summing to.
Cel...saving multiple sheets in a workbook
I have 1100 worksheets in a workbook all named after what I need to save them
as, but I can't find any kind of "bulk save" where Excel can take each of the
sheets and save them as individual files vs saving them all in a workbook.
I don't want to click on "Save as" for each of the files, then manually
enter the name of the worksheet, especially since there is no hotkey assigned
to "save as".
Does anyone know of a way around this? Or perhaps a shortcut for doing it?
Regards Ron de Bruin
http://www.ro...View multiple calendars in one
I want to create multiple calendars in one instance of
Outlook. I then want to have a calendar that consolidates
all entries in all the other calendars. Any ideas if/how
this is possible?
Creating sub-calendars is easy. Consolidating them all onto one is hard.
I wouldn't do it that way myself, instead I would use categories and views.
Have a single calendar and create categories for the various sub-calendars
you want to have. You can then use custom views to show you only the "calendars"
you want to see at any given time.
Ben M. Schorr, OneNot...Values in multiple columns into one column
I have a spreadsheet with values in multiple columns
where for instance col A is blank colb or c or d won't
be...there will be something in one where the others are
empty. I need to create a column with all the values
together. Tried using the following formula but it
doesn't seem to work... the values are all dates..
=IF(O2=" ",N2,IF(N2=" ",K2,IF(K2=" ",J2,O2)))
Can anybody help!!
>I hav...Counting Individuals NOT Occurrences with than one criteria
I have a Excel 2007 Spreadsheet with 26 columns and 190 rows arranged as
A B C D E
1 Harm Red Fully Ach Abstinence Partly Ach Harm Red Fully Ach
2 Harm Red Fully Ach Harm Red Fully Ach
3 Harm Red Partly Ach
4 Abstinence Not Ach Harm Red Partly Ach
5 Abstinence Partly Ach
6 Harm Red Fully Ach Harm Red Partly Ach
7 Harm Red Fully Ach
Now I've been trying to use SUMPRODUCT and it only gives occurrences of say
'Harm Red' and 'Ful...Outlook & Multiple SMTP
I'm trying to set up outlook 2000 to allow the user to
select between different SMTP addresses when sending a
mail. In 2002, I have it set so that when I write a new
message, I can choose the account that's sending (and if
I don't choose it uses the default account.)
I have a friend using Outlook 2000 and she has 3-4 email
accounts set up, but she has no choice in which account
the outgoing mail is sent from.
Does it have anything to do with Internet Only vs.
Yes - you want IMO mode for multiple smtp setup.
"Jane" <noel_x...Multiple columns into 0ne
We get a lot of information that comes into different columns, say Product
1 - 88. We never know how many values there are in every colum or how many
Products there are
Now we want a macro that puts all the values into one column, say A - can be
in sheet 2. We do not want empty rows or columns but it is very important
that the figures are in the original order.
More information is needed. Is there a header row? If so, what do you
want to become of it? Might the be empty cells within the table? How
do you recognize how many columns are used, or is the first row alw...Refreshing Form with Multiple Forms and Fields
I have a main screen form that allows entry to start from that location. It
has four subforms and some other fields that are picked from other locations
to filter the record. My challenge is it does not Refresh right away. I
have tried putting Refresh and Repaint etc in the main form code but it still
doesn't refresh. I put a Refresh button on it and that works but it's slow.
Can anyone tell me what would be a more efficient method to get this screen
to refresh right away.
...Consolidate multiple investment accounts?
I have a question that I can't seem to find the answer for anywhere.
I have a Roth IRA with American Funds that consists of 4 funds. When I
connect to American Funds during account setup, instead of creating one
account for the Roth IRA, I get 4 accounts...one for each fund. Is there a
way to consolidate all of these individual fund accounts into one overall
Roth IRA account?
At first, this wasn't a big deal, but my wife and I both have a Roth IRA
with American, and my wife just added a 401k rollover with American. As a
result, we have 15 individual "accounts" inst...Multiple Records 1 Field
I need to create a report where I consolidate multiple records from 1 table
and join them to 1 record in another. The problem is, I need them to all be
joined into 1 record.
tblStudents (Name, LocID, Grade) - Single record per student
tblMods-Math (ModID, LocID, Mod) - Multiple mods for 1 kiddos is normal
Finished product needs to be
SomeQuery (Name, LocID, Grade, Mod1, Mod2, Mod3, Mod4, etc...)
How do I do that? I tried creating a crosstab query, but that didn't work out.
Thanks As Always
Rank the Mod values, per LocID. One possible solution is:
SELECT a.locID, a.mod, COU...How can I view files chronologically when opening multiple files
I often open multiple invoices for the same account, and I want to view them
beginning from the oldest to the most current. However, Excel situates them
in reverse order such that when I close one or do "ctrl tab" to step to the
next file it always steps to the newest file. I would prefer the "stepping"
begin and proceed with the oldest files.
I don't know that we have any control over the order Excel opens workbooks
or stacks their windows. You can press Ctrl-Shift-Tab to go in the reverse
"Stevilsize" <Stevilsize@discussions...Multiple modal dialog or ::MessageBox, crashes the application
We have an application, in which we need to show multiple modal dialog
boxes at the same time. But, when we close these modal dialog boxes,
the application crashes.
The messagebox is invoked as shown..
::MessageBox(hWndParent, m_strMsg, g_strTitle, MB_TASKMODAL |
Here is the call stack..
524.84c): C++ EH exception - code e06d7363 (first chance)
(524.494): Access violation - code c0000005 (first chance)
First chance exceptions are reported before any exception handling.
This exception may be expected and handled.
eax=001804ee ebx=00fb4cd0 ecx=0012d5f8 edx=7ffeff0...Sumif #2
I'm finding @sumif is not summing all numbers in a range. Does the=20
criteria have to be in a certain format? I believe I've fixed all =
discrepancies. Any other suggestions?
Regardless of format, if some of your numbers were entered as Text,
then they remain text and won't be counted. To coerce text numbers
to real numbers, copy an empty cell, select your numbers, choose
Edit/Paste Special, selecting the Values and Add Radio buttons.
In article <firstname.lastname@example.org>,
"Sum Problem" <email@example.com> wr...counting multiple columns
I'm using the following formula(s) to count how man instances of a
certain number I have in three different columns. The problem is that
it is working for most of the numbers but not for others. Only the
numbers 1-12 are in the cells.
Here are two examples. The top forumula is for counting the number of
cells in the three columns that contain the number 2. The bottom
formula does the same thing for the number 4. I get the right answer
for the "4" formula, but for the "...Remove Multiple items from a Site
Is there a way to remove multiple items from a site all at once? I have been
using the Item Quantities Matenence window, but can only do one item at a
time. I have several hundred items to remove.
I've done it with a macro, but I agree it should be part of the
software...if you can add by ranges, why not delete?
-Go to Tools/Macro/Record
-give the macro a name
-record yourself deleting one (including pressing the next arrow at the
bottom, if they're in sequence)
-go to Tools/Macro/Stop Record
You can either then run it via Tools/Macro/Play (or Ctrl-F8)...Printing multiple worksheets in 2000
How do I print multiple worksheets as one print job in
Select the Sheets you want(hold the Ctrl key when you select them) and print
Don't forget to ungroup
Or use this macro lines
'the whole workbook
'all sheets in the array
'print all selected sheets
'only the activesheet
'only "Sheet1...saved as single page now need to convert to one large multiple pag
i have saved several individual pages as single files in publisher now i need
to convert all of these individual saved files back into one multiple page
document cut and paste does not work how do i do this please help
> i have saved several individual pages as single files in publisher
> now i need to convert all of these individual saved files back into
> one multiple page document cut and paste does not work how do i do
> this please help
Which version of Publisher?
Why doesn't copy/paste work?
Maybe the following link will offer some ide...Money SB 2004 and multiple companies?
Hi. Question for anyone. I'm an independent small
business owner and own 3 different small companies. Can
MS Money SB 2004 support multiple companies?
Not in one Money file. You will need 3 different files.
>Hi. Question for anyone. I'm an independent small
>business owner and own 3 different small companies. Can
>MS Money SB 2004 support multiple companies?
"Doug Edwards" <firstname.lastname@example.org> wrote in message
> Not in one Money file. You will need...Multiple filters using combo box
I'm trying to figure out how to filter a query using the following criteria:
Start Date and End Date
Issue or All
Agent or All
Rep or All
In each combo box I have the following:
SELECT [tbl_Issue].[Issue] FROM tbl_Issue union select ' All' FROM tbl_Issue
ORDER BY [Issue];
In the query I have the following:
Date: >=[Forms]![frmselector]![txtStartDate] And
Issue: Like [Forms]![frmselector]![Issue]
However, I get a blank query. What am I missing?
I suggest you download a sample database that uses ...Combining multiple columns of text
I have a file of addresses. Each individual line of the address is in a
different adjacent cell. I would like to be able to combine the text into
one cell that is separated by commas, and or spaces. Is there an easy way to
Are you making labels?
If yes, then keep your data the way it is. It's much easier to combine the
values than to separate it.
Use excel as your data base and use MSWord as the application to make the
You may want to read some tips for mailmerge.
http://www.mvps.org/word/F...HELP! Procedure or Program suggestion for finding multiple - the last 4 occurances
This is a multi-part message in MIME format.
I need help in finding a procedure or program suggestion for finding =
multiple - the last 4 occurances - of recurring appointments in Outlook.
The standard "find" seems to have a bug in that it will not find all =
occurances of recurring appointments, it will not find the last series =
of a recurring appointments -i.e. the last months worth that has already =
occured - and in general returns ...Multiple Trigger Firing
We have a client who is claiming that a trigger is firing multple times. We
are setting up the trigger at Startup with the following code.
ret = Trigger_RegisterFocus(
anonymous('OK Button' of window 'UPR_Calculate_Checks' of form
As you can see, this trigger is registered to fire on the OK Button click on
the Calculate Checks screen. The only thing that happens in OurScript is a
connection to a database, a call to a stored procedure, and the c...Update field (multiple criteria and updates)
I would like to update 2 fields with the following
Where [item description] like "*BASIC*" update [item description] = "BASIC"
and [item] = "HITS"
Where [item description] like "*FARE*" update [item description] = "FARE"
and [item] = "HITS"
Where [item description] like "*SEARCH*" update [item description] =
"SEARCH" and [item] = "HITS"
I typically use design view to set up queries and I can only do one of the
three changes in this view. Is there are way to write this in sql to do all
thr...Combining two excel sheets into one, where one sheet has multiple values?
I have two excel sheets Sheet 1, and Sheet 2 within one Excel document.
Sheet 1 has columns Name of a Person, PersonIDnumber
Sheet 2 has columns PersonIDnumber, Email address of that person
Combining them into one sheet which has Name of Person, PersonIDNumber,
Email address can be done using
The issue is Sheet 2 has multiple rows for the same PersonIDnumber
PersonIDnumber Email address of person
Is there a way I can combine S...Help Adding up multiple calculated cells
I have 8 columns that MAY contain NOTHING or may contain text chosen
from a lookup name from a data validation pick list. Lic3 won't
contain a value UNLESS Lic1 AND Lic2 are populated.
I have 8 columns range named Lic1, Lic2, etc to hold a chosen license
I have 8 other columns used for number counts relating to the lookups
I have range named Lic1Cnt, Lic2Cnt, etc which hold a number such as
I need to add up the value calculated for each Lic based on looking up
the text in a table, getting the price and then multiplying by the Lic?
Cnt number. Some records may have only a singl...