Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct or countif
Hi, I need to sum numbers in one column based on 2 critarias from another
column, I have formula as =SUMPRODUCT(--($O$20:$O$79="a"),--($O$20:$O$79="b"),$Q$20:$Q$79) but the result showed 0. Am I correct for using sumproduct or shall I use countif (which I haven't learned yet)? Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct or countif
So, the criteria is: O20:O79 = A *OR* B
Try one of these: =SUMPRODUCT((O20:O79={"a","b"})*Q20:Q79) =SUM(SUMIF(O20:O79,{"a","b"},Q20:Q79)) -- Biff Microsoft Excel MVP "freebee" wrote in message ... Hi, I need to sum numbers in one column based on 2 critarias from another column, I have formula as =SUMPRODUCT(--($O$20:$O$79="a"),--($O$20:$O$79="b"),$Q$20:$Q$79) but the result showed 0. Am I correct for using sumproduct or shall I use countif (which I haven't learned yet)? Thank you. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct or countif
=SUMPRODUCT(--(A1:A10="a")+--(A1:A10="b"),B1:B10)
change column letters row numbers according to your set up.. "freebee" wrote: Hi, I need to sum numbers in one column based on 2 critarias from another column, I have formula as =SUMPRODUCT(--($O$20:$O$79="a"),--($O$20:$O$79="b"),$Q$20:$Q$79) but the result showed 0. Am I correct for using sumproduct or shall I use countif (which I haven't learned yet)? Thank you. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct or countif
Hi,
The first one did not work, but the 2nd one worked, thank you so much. "T. Valko" wrote: So, the criteria is: O20:O79 = A *OR* B Try one of these: =SUMPRODUCT((O20:O79={"a","b"})*Q20:Q79) =SUM(SUMIF(O20:O79,{"a","b"},Q20:Q79)) -- Biff Microsoft Excel MVP "freebee" wrote in message ... Hi, I need to sum numbers in one column based on 2 critarias from another column, I have formula as =SUMPRODUCT(--($O$20:$O$79="a"),--($O$20:$O$79="b"),$Q$20:$Q$79) but the result showed 0. Am I correct for using sumproduct or shall I use countif (which I haven't learned yet)? Thank you. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct or countif
The first one did not work
You must have some entries other than numbers in the sum range if it didn't work. -- Biff Microsoft Excel MVP "freebee" wrote in message ... Hi, The first one did not work, but the 2nd one worked, thank you so much. "T. Valko" wrote: So, the criteria is: O20:O79 = A *OR* B Try one of these: =SUMPRODUCT((O20:O79={"a","b"})*Q20:Q79) =SUM(SUMIF(O20:O79,{"a","b"},Q20:Q79)) -- Biff Microsoft Excel MVP "freebee" wrote in message ... Hi, I need to sum numbers in one column based on 2 critarias from another column, I have formula as =SUMPRODUCT(--($O$20:$O$79="a"),--($O$20:$O$79="b"),$Q$20:$Q$79) but the result showed 0. Am I correct for using sumproduct or shall I use countif (which I haven't learned yet)? Thank you. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct or countif
Try it like this:
=SUMPRODUCT((A1:A10="a")+(A1:A10="b"),B1:B10) -- Biff Microsoft Excel MVP "JMay" wrote in message ... =SUMPRODUCT(--(A1:A10="a")+--(A1:A10="b"),B1:B10) change column letters row numbers according to your set up.. "freebee" wrote: Hi, I need to sum numbers in one column based on 2 critarias from another column, I have formula as =SUMPRODUCT(--($O$20:$O$79="a"),--($O$20:$O$79="b"),$Q$20:$Q$79) but the result showed 0. Am I correct for using sumproduct or shall I use countif (which I haven't learned yet)? Thank you. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct or countif
Hi,
A totally different idea - suppose there are titles on row 19, directly above the data, and suppose the one in O19 is "Letter". In a separate range enter Letter a b Suppose this is in D1:D3 The you can use the formula =DSUM(O19:Q79,3,D1:D3) The D-functions are under employed because they require a criteria range somewhere else in the spreadsheet. But they do produce simple easy to understand formulas regardless of how complex the criterial. -- Thanks, Shane Devenshire "freebee" wrote: Hi, I need to sum numbers in one column based on 2 critarias from another column, I have formula as =SUMPRODUCT(--($O$20:$O$79="a"),--($O$20:$O$79="b"),$Q$20:$Q$79) but the result showed 0. Am I correct for using sumproduct or shall I use countif (which I haven't learned yet)? Thank you. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct or countif
You don't need the double unary minus if you've got the addition. The
arithmetic operation is enough to coerce the conversion from boolean to number. Double unary minus (or double unary negation if you prefer) is just a way of forcing an arithmetic operation where you don't already have one. You'll see that if you use the SUMPRODUCT((condition1)*(condition2)) format you don't need the double unary minus, but with SUMPRODUCT(--(condition1),--(condition2)) [comma instead of multiply] you do need it. -- David Biddulph "JMay" wrote in message ... =SUMPRODUCT(--(A1:A10="a")+--(A1:A10="b"),B1:B10) change column letters row numbers according to your set up.. "freebee" wrote: Hi, I need to sum numbers in one column based on 2 critarias from another column, I have formula as =SUMPRODUCT(--($O$20:$O$79="a"),--($O$20:$O$79="b"),$Q$20:$Q$79) but the result showed 0. Am I correct for using sumproduct or shall I use countif (which I haven't learned yet)? Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF or SUMPRODUCT | Excel Discussion (Misc queries) | |||
sumproduct or countif | Excel Discussion (Misc queries) | |||
CountIf or sumproduct | Excel Discussion (Misc queries) | |||
Sumproduct or countif? | Excel Discussion (Misc queries) | |||
COUNTIF or SUMPRODUCT? | Excel Worksheet Functions |