# Using Sumproduct

#### amcather

##### New Member
I am currently using the SUMPRODUCT to sum a value if it is in two date ranges. I want to sum a group of numbers when they don't = "N/A". We can use a previous column where N/A maps to 0. I have been using the following formulas but I get a #Value. When i have broken it out to

R= column with N/A, Q = column with 0
=SUMPRODUCT(--('7.1 - IPPS Review'!\$R\$2:\$R\$10002),--('7.1 - IPPS Review'!\$B\$2:\$B\$10002>=Start_Date),--('7.1 - IPPS Review'!\$B\$2:\$B\$10002<=End_Date),--('7.1 - IPPS Review'!Q2:Q10002<>"0"))

=SUMPRODUCT(--('7.1 - IPPS Review'!\$R\$2:\$R\$10002<>"N/A"),--('7.1 - IPPS Review'!\$B\$2:\$B\$10002>=Start_Date),--('7.1 - IPPS Review'!\$B\$2:\$B\$10002<=End_Date),
)

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Try this.

In your 1st example, change "0" to just 0.

Try:

=SUMPRODUCT(--(NOT(ISNA('7.1 - IPPS Review'!\$R\$2:\$R\$10002))),--('7.1 - IPPS Review'!\$B\$2:\$B\$10002>=Start_Date),--('7.1 - IPPS Review'!\$B\$2:\$B\$10002<=End_Date),--('7.1 - IPPS Review'!Q2:Q10002<>"0"))

how to sum w/ N/A in array

Thanks for your reply. That works except it tries to sum the "n/a" how can i use that formula but but criteria not to sum "n/a". For example,

The values in colunmn Q are N/A, N/A, 0, 1, 2, 5, 7 - the sum should be 15 however i get #value. Is this feasible?

Are these #N/A or just N/A?

If Q2 on sheet "7.1 - IPPS Review" contains the formula:

=IF(ISNA(R2),0,R2)

copied down, you could use:

=SUMPRODUCT(--('7.1 - IPPS Review'!\$B\$2:\$B\$10002>=Start_Date),--('7.1 - IPPS Review'!\$B\$2:\$B\$10002<=End_Date),('7.1 - IPPS Review'!Q2:Q10002))

to sum the the values in column R that are not #N/A.

Replies
4
Views
207
Replies
22
Views
499
Replies
1
Views
249
Replies
4
Views
376
Replies
5
Views
169

1,196,252
Messages
6,014,265
Members
441,809
Latest member
pawansher2002

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back