Hi Everyone,
I'm looking for some help on a sumifs and averageifs formula
I want to sum cells in column A if any of the columns Q, R, or S equal "Private Payer" (and other criterias)
I've tried using this formula, but it returns a Value error
=SUMIFS(A2:A2000,Q2:S2000,"Private Payer",Z2:Z2000,"2013")
also
=Averageifs(A2:A2000,Q2:S2000,"Private Payer",Z2:Z2000,"2013")
How would I fix this?
Thanks!
Try something like:
=SUM(IF(ISNUMBER(SEARCH("Private Payer",Q2:Q2000&"x"&R2:R2000&"x"&S2:S2000)),A2:A2000,0)*IF(Z2:Z2000=2013,1,0))
as an array formula (confirm with ctrl+shift+enter)
you may need to play around with 2013 depending on the format of your data.
both SUMIFS and AVERAGEIFS need all ranges to be the same size so you can't use some ranges that are a single column and another that's three columns
Is it possible to have "Private Payer" more than once on a single row? If not then try this for a sum
=SUMPRODUCT((A2:A2000*(Q2:S2000="Private Payer")*(Z2:Z2000=2013))
and for the average this array formula
=AVERAGE(IF(Q2:S2000="Private Payer",IF(Z2:Z2000=2013,A2:A2000)))
confirmed with CTRL+SHIFT+ENTER
both of those formulas will double or treble count if you have "Private Payer" more than once in a single row
Audere est facere
Thank you! that worked perfectly,
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks