# trying to use multiple list criteria in sumif with comma, not working?

1. ## trying to use multiple list criteria in sumif with comma, not working?

Been banging my head on the keyboard for last 2 hours trying to figure out how I used to sum multiple criteria within a single range when using {"text","text"}, but not working.
This formula only gives me the 10-1result, and I need to add for columns up each for the day, with 16 different criteria??
Also, I don't really want a 15 line formula for only adding 4 criteria up over 4 columns. Is there a way to sum all 4 periods. P1,P2,P3,P4, only adding the 10-1, 10-2,10-3 criteria? 10-1,10-2 etc cells are dropdown lists.
=SUMIF(B3:B13,{"10-1","10-2"},C3:C13)

Sorry, my small file wouldn't upload?

name P-1 Hrs P-2 Hrs P-3 Hrs P-4 Hrs
a 10-1 8.1 13-2 1.0 NA NA
b 10-1 8.1 NA NA NA
y 10-1 8.0 NA NA NA
d 10-3 8.0 NA NA NA
r 10-1 7.5 NA NA NA
f 10-1 8.0 NA NA NA
g 10-2 9.0 NA NA NA
gh 10-1 10.0 NA NA NA
i 10-1 11.0 NA NA NA
j 10-2 14.0 NA NA NA
k 10-1 13.0 NA NA NA

2. ## Re: trying to use multiple list criteria in sumif with comma, not working?

Hello P - can't you go with something like:
Formula:
`Please Login or Register  to view this content.`

Cheers

3. ## Re: trying to use multiple list criteria in sumif with comma, not working?

Hi
Try this formula:
=SUMIFS(C2:C12,B2:B12,"=10-Jan")+SUMIFS(C2:C12,B2:B12,"=10-Feb")
Good luck.
Tony

4. ## Re: trying to use multiple list criteria in sumif with comma, not working?

Originally Posted by Prodschdler
Been banging my head on the keyboard for last 2 hours
I too have been banging my head on a brick wall about something similar to this. Here's what I found:

My formulae have somehow changed from

=SUMIF(Budget,\$U22,Withdrawals) to =SUMIF(Budget|\$U22|Withdrawals) where Budget and Withdrawals are named ranges. I did not make these changes. I have always used commas, not vertical bars. I guess this is from an automatic upgrade.

If I copy and paste a formula, it works in a different location.
If I type the formula by hand it does not work
If I edit a working forumula like the one above, to (say) =SUMIF(Budget|\$U2a|Withdrawals) (ie change one character), then I get an error message.

The help pages give the correct syntax as SUMIF(range, criteria, [sum_range])
The pop-up help dialog strip shows SUMIF(range|criteria|[sum_range]) - ie shows the vertical 'or' bar.

Then I cracked it. You need to have a space on either side of the vertical bar.

So =SUMIF(Budget|\$U22|Withdrawals) will not be accepted.
But =SUMIF(Budget | \$U22 | Withdrawals) will be accepted.

But if you come to edit the formula that you have just entered, the spaces will have been removed, and you have to put them back again for the edited command to work.

I hope that this saves a few heads and brick walls from being demolished.

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