Evening all,
I have tried to use the countifs function to find the totals of 1s a 4s in the table.
No luck yet.
Table attached.
Cheers,
John.
Evening all,
I have tried to use the countifs function to find the totals of 1s a 4s in the table.
No luck yet.
Table attached.
Cheers,
John.
Johnmitch93,
The formula in cell B27:
=SUMPRODUCT(($A$2:$A$25="NORTH")*($B$2:$F$25=4))
returns:
4
The formula in cell B28:
=SUMPRODUCT(($A$2:$A$25="NORTH")*($B$2:$F$25=1))
returns:
10
Have a great day,
Stan
Windows 10, Excel 2007, on a PC.
If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
Stanley,
That's good stuff, mate. Ta very much.
Is there an explaination why the countifs is no good? I was sure I had done it that way before.
Maybe my memory...
John.
Hello John, All the ranges must be in same number of rows & columns, if you have multiple conditions.
Excel help says,
Or, you can use COUNTIFS with OFFSET. Note OFFSET is a volatile functionEach additional range must have the same number of rows and columns as the criteria_range1 argument. The ranges do not have to be adjacent to each other
=SUMPRODUCT(COUNTIFS(A2:A25,"NORTH",OFFSET(B2:B25,0,{0,1,2,3,4}),4))
This is basically doing,
=COUNTIFS(A2:A25,"NORTH",B2:B25,4)+COUNTIFS(A2:A25,"NORTH",C2:C25,4)+...COUNTIFS(A2:A25,"NORTH",E2:E25,4)
Last edited by Haseeb Avarakkan; 01-27-2012 at 06:53 PM. Reason: Typo...
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
Well, that works too. Thanks Haseeb.
I am still puzzled why the countis do not work - I just checked and I do have one that works.
JOhn.
As Haseeb says, all ranges must be the same size. This, in fact, applies to all "IF" and "IFS" functions like SUMIF, COUNTIFS, AVERAGEIFS etc......because there's always a "one-to-one" relationship between the cells, e.g. in COUNTIFS(A2:B10,"x","C2:D10,"y") you are counting instances of "paired" cells where the conditions are met, e.g. A4 is paired with C4, B9 with D9 etc. Given that setup it doesn't make sense to allow the ranges to be different sizes.....
Note that SUMIF (and AVERAGEIF) allows you to "apparently" set ranges to different sizes, e.g.
=SUMIF(A:C,"x",D:D)
....but, in fact, the sum range always becomes the same size (and shape) as the criteria range so with that formula if you have "x"s in columns B and C then the corresponding cells in columns E and F are summed......even though those columns aren't included in the formula.
Audere est facere
I am OK with that. When the erroror return is #VALUE! does it mean I have written the function correctly?
John.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks