Excel Q - SUMIFS with a 2 dimensional array

excel excel-formula excel-2010 vba
Joseph Kadoch picture Joseph Kadoch · Jul 19, 2015 · Viewed 75.6k times · Source

I have a 2-D array: dates on a horizontal axis and identification numbers on a vertical axis.

I want the sums conditioned on a particular date and ID, and I want to know how to do this using SUMIFS.

For some reason, it seems like I cannot since the array is 2-D while the criteria ranges are 1-D. Can anyone give me any advice on other formulas I can use?

In other words, I would like to add the values that satisfy the ID and date I select; there is one or more data point that satisfies the conditions. This is why the SUMIF function is relevant.

Answer

rwilson picture rwilson · Jul 19, 2015

With this data you will not be able to use a SUMIF forumula. Here's a formula you can use:

=SUM(IF($B$2:$B$6=C9,IF($F$1:$K$1=B9,$F$2:$K$6)))

Change the addresses where appropriate and be sure and enter it by pressing CTRL + SHIFT + ENTER. You can also use the below formula to avoid pressing CTRL + SHIFT + ENTER:

=SUMPRODUCT(($B$2:$B$6=C9)*($F$1:$K$1=B9)*$F$2:$K$6)

enter image description here

Related questions

Excel how to find values in 1 column exist in the range of values in another

I have two columns- column A which extends upto 11027(values) and column I which extends to 42000(values).Both the columns contains some code details. Something like this A B q123 as124 as124 gh456 ff45 q123 dd1 dd2 xx2 xx3 xx4 …

Read More
Remove leading or trailing spaces in an entire column of data

How do I remove leading or trailing spaces of all cells in an entire column? The worksheet's conventional Find and Replace (aka Ctrl+H) dialog is not solving the problem.

Read More
Ignore Duplicates and Create New List of Unique Values in Excel

I have a column of values that often appear as duplicates. I need to create a new column, of unique values based on the first column, as follows: Column A Column B a a a b b c c c …

Read More