Using two conditions with a SUMIF function in Excel 2003

For a given row on Sheet1, I use the SUMIF function to see if the value from the first cell of that row is on a list of historical values in Sheet2.

That will basically summarize all the values on my historical sheet and group them by my A1 cell name on Sheet2.

I need a second condition so that I can group rows by month also, and the SUMIF does not seem to allow for a second condition or an AND to concatenate a second clause.

Replay

Assumptions:

Dates are in column B

Rows are 5:29

historical list in a range name 'Historical'

Month to test is in a range name 'rngMonth'

=SUMPRODUCT((NOT(ISNA(MATCH(A5:A29,Historical,FALSE))))*(MONTH(B5:B29)=rngMonth)*(C5:C29))

Try to add another SUMIF function which excludes those cells which you don't want to be SUMMed up & remove it from the main SUMIF.

e.g. SUMIF(all cells which fits large criteria) - SUMIF(all cells which are part of small criteria).

Alternate: on the sheet with historical data, add a column that concatenates the two columns you want to use as the combined conditions. For example, suppose one condition column is range-named "Month", the other condition column is range-named "PersonName":

The new column contains the following formula:

=Month&" "&PersonName

Name this column (for example, "Criteria") then use that range name in the SUMIF. For example:

=SUMIF(Criteria,"January Biff",ValsToBeSummed)

Use array formulas like this:

{=sum(If(sheet2!A1:A50=sheet1!A1,if(sheet2!B1:B50=Sheet1!B1,Sheet2!C1:C50)))}

Basically you nest your IF statements inside a SUM statement, then press Ctrl+Shift+Enter to make it an array formula.

Introduction to Array Forumulas (as posted by Jon Fournier) is also good resource on this.

Category: microsoft excel Time: 2009-02-23 Views: 1

Related post

  • How to use the autocomplete feature for VBA function in Excel 2007 with Excel Add-In 2012-07-15

    (cloning from question on SO) I created a function in VBA. I want Excel 2007 to show the Autocomplete when writing this function in the cell's Excel. Detail as How to use the autocomplete feature for VBA function in Excel 2007 with Excel Add-In (.xla

  • Using Outlook 2007 with the rest of the Office 2003 suite 2010-02-27

    Is it possible to use Outlook 2007 with the rest of the Office 2003 suite? Are there incompatibilities which will cause problems? --------------Solutions------------- Yes, you can install different versions of different Office products simultaneously

  • Two conditions with if functions 2015-12-13

    Please help me figure out the following: Dependency on two cells to arrive at the results E.g cell A2 is today() and cell a3 is null then result should be "add". If cell a2 is any backdate and cell A3 is null then result should be "pending&

  • What is the best way to use two keys with a std::map? 2009-07-11

    I have a std::map that I'm using to store values for x & y coordinates. My data is very sparse, so I don't want to use arrays or vectors, which would result in a massive waste of memory. My data ranges from -250000 to 250000 but I'll only have a few

  • How should I handle using two databases with a legacy PHP application? 2012-07-03

    I have a legacy PHP application that was written in 2004 and uses MSSQL as a database backend. At this stage MSSQL is still supported by PHP but only just via a Microsoft driver. I have looked at converting to mysql via automated tools, which work qu

  • How can I use two monitors with my radeon 6450 graphics card? 2013-03-09

    I have a computer with an integrated gpu on the motherboard. Because I wanted to use two monitors and my motherboard only has one VGA port, I bought an ATI radeon HD 6450. Both my monitors have a VGA connector, so I also bought an adapter (VGA to DVI

  • AlwaysOn Automatic Failover Using Two Nodes with SQL Standalone Instances 2013-03-28

    We are building a new SQL 2012 cluster for our web environment. We decided to use two nodes and utilize AlwaysOn Availability Groups for High Availability. Server01 and Server02 have had a Standalone Instance of SQL installed and both have been joine

  • How to use two gateways with the same IP address? 2013-09-17

    I have to configure a NAT and manually split internet traffic between two gateways which are connected to a Linux box. There was no problem if each gateway has a specific IP address. I can do this by iproute2: ip route add default via 192.168.1.1 dev

  • Windows File "open with" cannot use two applications with the same executable name 2013-12-04

    One can choose what application to use for opening a file with Right click on the file Open with Choose default program... Browse In this way, one navigates to the directory (say, C:\dir_new) containing the intended app (say, myapp.exe). If it happen

  • Using two keyboards with different layouts simultaneously 2014-12-20

    I am using a MacBook with a UK keyboard layout and a USB keyboard also with a UK layout. However, some of the keys are assigned differently on the two keyboards. For example, Shift-2 is @ on one keyboard, and " on the other. The key to the left of 1

  • Is it possible to use two keyboards with two different keymaps? 2015-04-22

    Basically, what I am wanting to do is use a keyboard (i.e. in this case my Razer Black Widow as my main keyboard functioning as a keyboard with whatever my default keymap is at the time) and use my other keyboard (i.e. Razer Anansi as a soundboard/la

  • use two keyboards with independent Num Lock in Windows 7 2015-06-16

    I need to use two keyboards: one up on shelf on my desk so I can use its numpad, and one which is compact so I can fit it alongside my Wacom tablet. It works great except that on the compact keyboard, some of the letters are used as a numpad when Num

  • Horizontal scrolling with mouse wheel/keyboard in Excel 2003? 2010-09-20

    How can I scroll horizontally in Excel 2003 using the mouse wheel with the keyboard? Operating system is Windows XP. --------------Solutions------------- If you press down the scroll wheel on most mice, that shows a centre point on screen and then as

  • COUNTIF using two conditions from data inside and outside the dataset 2013-11-25

    I'm struggling to use numbers to do a function. Specifically, I want to use COUNTIF to check two things within my data set table and put that information into another table that we'll call "query set". I want to check for one variable that is wi

  • Using Paste Special Values with the HYPERLINK function in Excel 2009-09-26

    I have a long list of data in Excel 2007. Using the Hyperlink function: =HYPERLINK(C2,A2) I've added individual links to them all. Now I want to get rid of the function and just leave the data with the link. If I do Paste Special > Values, it does th

  • Can't use two screens with an Nvidia 9600GT 2011-10-18

    In an effort to learn more about Ubuntu, I downloaded and installed 11.10 onto my old workstation at home. We have a second workstation that runs Windows 7 too. Both workstations are plugged into both monitors. The Win7 one uses DVI into a HP LA2405w

  • An odd performance problem rendering a simple scene (less than 14k vertices) in OpenGL using two vbos with LWJGL 2012-04-26

    Problem I have been having a strange degrading performance issue rendering a simple scene containing two "chunks" of 4x4x4 cubes each. Video of problem This is a screen capture showing my console output, look specifically at the FPS dropping (wh

  • What is a better way to present two elements with "load more" function 2013-11-12

    Here is the problem: Two element. Instagram and Flickr. They both show 4 images then the user has to click on Load more to load the next 4 images. I think it would be pretty annoying for the user to click the load more button every time. Can you thin

  • Fault tolerance by using two IP with two interfaces 2015-01-29

    I would like to have a fault-tolerant setup for http requests. For brevity, let's assume we have a very simple Web-service and there is no difference which server responses. So I will have the following setup (all three are in the same LAN): Upstream

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 4.960 (s). 13 q(s)