Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 19
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 468
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 19
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,344
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
COUNTIF or SUMPRODUCT Jimmy1982 Excel Discussion (Misc queries) 1 October 16th 08 01:55 AM
sumproduct or countif dr350x Excel Discussion (Misc queries) 7 June 16th 07 03:27 PM
CountIf or sumproduct timmulla Excel Discussion (Misc queries) 1 January 18th 07 04:49 AM
Sumproduct or countif? phatbusa Excel Discussion (Misc queries) 9 December 13th 06 11:48 PM
COUNTIF or SUMPRODUCT? scott Excel Worksheet Functions 4 August 28th 06 03:51 AM


All times are GMT +1. The time now is 02:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"