# sumifs with mulitple columns and mulitple criteria in each column

1. ## sumifs with mulitple columns and mulitple criteria in each column

I am having trouble writing a function in excel that will count the number (similar to countifs function) and sum the \$ amount (like the sumifs function, but that was not working due to the multiple columns with multiple criteria options in each column).

Example:

Sum Column R, IF

Column AB = 1C1 or 1C2A or 1C2B or 1A1 or 1D AND
Column Z = 128 or 160 or 650 or 125 or 641 AND
Column X = 1 or 2 or 3 or 5 or 6 or 7 or 10 or 12 AND
Columns AN = 005 or 012 AND
Column O = <2011000

Please advise if anyone knows of a function. This is my first post, so please let me know if more detail is required.

2. ## Re: sumifs with mulitple columns and mulitple criteria in each column

Try:

``Please Login or Register  to view this content.``
where column A:A is column to sum up.

3. ## Re: sumifs with mulitple columns and mulitple criteria in each column

It returned a "#value" is there a quick fix, this is the first time I have not gotten an error message so that is progress. Thank you.

---------- Post added at 12:42 PM ---------- Previous post was at 12:42 PM ----------

It returned a "#value" is there a quick fix for that?

4. ## Re: sumifs with mulitple columns and mulitple criteria in each column

What is the actual formula you used? Are ranges all same size?

5. ## Re: sumifs with mulitple columns and mulitple criteria in each column

=SUM(SUMIFS(AllLoans!R2:R5223,AllLoans!AB2:AB5223,{"1C1","1C2A","1C2B","1A1","1D"},AllLoans!Z2:Z5223,{128,160,650,125,641},AllLoans!X2:X5223,{1,2,3,5,6,7,10,12},AllLoans!AN2:AN5223,{"005","012"},AllLoans!O2:O5223,"<2011000"))

I actually noted that I had typed 5233 in one of the columns, so now it is just returning "0", but I know there should be a some that work. These are "OR" criteria correct?

6. ## Re: sumifs with mulitple columns and mulitple criteria in each column

If these columns: AllLoans!Z2:Z5223 and/or AllLoans!X2:X5223 are text values, then put quotes around the entries in corresponding arrays: {128,160,650,125,641} and or {1,2,3,5,6,7,10,12} in the formula...

7. ## Re: sumifs with mulitple columns and mulitple criteria in each column

I tried that and it did not work. Is there a way to make sure there are not any hidden spaces after the text in the columns that are prohibiting a return value.

8. ## Re: sumifs with mulitple columns and mulitple criteria in each column

you would have to clean them.. perhaps using FIND/REPLACE replacing " " with "" (i.e. space with null)

9. ## Re: sumifs with mulitple columns and mulitple criteria in each column

It is still returning 0. Any other thoughts? I really appreciate you taking the time to think through this.

10. ## Re: sumifs with mulitple columns and mulitple criteria in each column

Post the workbook...

11. ## Re: sumifs with mulitple columns and mulitple criteria in each column

sample.xlsx

I had to alter the data due to sensitive information, but the sample should provide the idea. I have updated the formula on the summary tab.

12. ## Re: sumifs with mulitple columns and mulitple criteria in each column

Apparently I might have messed that up... try instead...

``Please Login or Register  to view this content.``

13. ## Re: sumifs with mulitple columns and mulitple criteria in each column

That worked on the sample sheet. I will apply to the original workbook. Thank you so much for your help. I really appreciate it.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1