Hi everyone,
I have a large database, that basically looks similiar to the one I have in the example Excel.
Staying with the example, I would like to create a sumifs-formula (or something else that works) that looks in the table for a specific column and runs a sumifs formula based on some other criteria.
In the excel, for example, I would like to check country A for climate 1. In this column I want to get the sum of all age 2.
Does anyone know how to do this?
Thanks in advance!
Hi there. try this out:
=SUMPRODUCT(($D$3:$L$3="A")*($D$4:$L$4=1)*($B$5:$B$10=2)*$D$5:$L$10)
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Hi Glenn, thanks a lot, that works perfectly!
Is there a way to use this as a count formula as well? I.e. Excel counts, e.g. all cells that are greater than 0?
Yes... try this:
=SUMPRODUCT(($D$3:$L$3="A")*($D$4:$L$4=1)*($B$5:$B$10=2))
I think I get it. But doesn't this formula just count the cells that fulfill the criteria?
Can I add an explicit assumption that the formula should count only values above 0?
Oops missed that bit.... Im in the middle of a 5 mile walk. Look back a bit later. ..
OK. here you go...
=SUMPRODUCT(($D$3:$L$3="A")*($D$4:$L$4=1)*($B$5:$B$10=2)*($D$5:$L$10>0))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks