• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

sum countifs and ranges

B

bfraser

Member
I have a workbook w/15 worksheets. Each ws has 1 or 2 named ranges for date col and name col. Using this formula to sum two criteria- persons name and a date(...today -365).


=SUM(COUNTIFS(brtech1,B38,brdate,">="&$K$15),COUNTIFS(cbtech1,B38,cbdate1,">="&$K$15),COUNTIFS(cbtech2,B38,cbdate2,">="&$K$15),COUNTIFS(cstech1,B38,csdate1,">="&$K$15),COUNTIFS(cstech2,B38,csdate2,">="&$K$15),COUNTIFS(fctech1,B38,fcdate1,">="&$K$15),COUNTIFS(fcttech1,B38,fctdate1,">="&$K$15))


1) I get the correct sum until the last countifs then I get #VALUE error. Still need to add more countifs.

2) Is there an easier way to sum countifs with multiple named ranges and worksheets. I'll end up w/a lot of countifs.
 
Nice formula, Not!


Apart from simplifying the named ranges more eg: CBD1, CBD2, CBT1, CBT2

or maybe shifting all the data to 1 large table, may not be possible or practical

I can't see any other easy solutions

Can any of the logic be replaced with a Sumproduct which may get rid of a few formula's?

Can the multiple tables be combined within a Pivot table and appropriately extracted/summarised?
 
hi, you can actually use sum product


To count use this


=SUMPRODUCT(COUNTIF(INDIRECT(brtech1,B38,brdate),">="&$K$15)).


To sum use this


=SUMPRODUCT(sumIF(INDIRECT(brtech1,B38,brdate),">="&$K$15)).
 
This is a list of the Named Ranges in the above formula

brdate

brtech1

cbdate1

cbdate2

cbtech1

cbtech2

csdate1

csdate2

cstech1

cstech2

fcdate1

fctdate1

fctech1

fcttech1


Just checking but fcttech1 , looks out of place
 
Nothing worse than getting slammed by Hui!!! I'll shake it off, go home, put my kids on the bus and step in front of it.
 
Please don't take it as a slammming, as it wasn't intended to be

Unfortunately from time to time we end up with these mothers of formulas

I've posted a few classics here as solutions

It's not half as bad as the solution

=TREND(OFFSET($J$6,MATCH($C7,$J$7:$J$16,1),MATCH($D7,OFFSET($K$6:$P$6,MATCH($C7,$J$7:$J$16,1)-1,0)),1,2),OFFSET($J$6,MATCH($C7,$J$7:$J$16,1)-1,MATCH($D7,OFFSET($K$6:$P$6,MATCH($C7,$J$7:$J$16,1)-1,0)),1,2),$D7)


I posted for

http://chandoo.org/forums/topic/uni-assingment-help-matching-prices-for-shopping-items
 
Just a little humor. Acutally, I have all the date in one Table/Pivot Table, for my own use. I even, excuse me, Hui even wrote code for a my "chessy" userform that makes entering data oh so simple but my oh so incompetent office mates are letting it collect "dust" and refuse to use it. They don't even open the Pivot Table.
 
If the data is in one table it may be a lot easier and more concise to extract what your after

Do you want to explore those techniques?
 
You must log in or register to reply here.
Back
Top