Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-05-2021, 01:36 PM
SoMany SoMany is offline Mix COUNTIF and SUMPRODUCT? Windows 7 64bit Mix COUNTIF and SUMPRODUCT? Office 2016
Advanced Beginner
Mix COUNTIF and SUMPRODUCT?
 
Join Date: Oct 2016
Posts: 51
SoMany is on a distinguished road
Default Mix COUNTIF and SUMPRODUCT?

I'm trying to count the number of cells starting with "ABC", and the first three numbers are odd. And the cells next to them say "Here". The cells will say something like ABC21710.

I don't even know the what formula needs to be outside, and what formula needs to be inside. I can do a COUNTIFS formula, and a SUMPRODUCT formula to find odd numbers when the whole number is odd, but all of the numbers are technically even.... I'm lost. There's an example spreadsheet attached.



Please help

Edit for clarification;
- Starts with "ABC"
- Ends with 5 numbers, all of them are technically ever. I'm looking for the odd numbers, of the first 3 numbers listed. And actually, if there's a way to look at the 6th alphanumeric in, which is all that matters here, then that would work. For example "ABC12310", a formula that would look for the 3, which is the 6th alphanumeric in the example.
- Lists "Here in the neighboring cell.
Attached Files
File Type: xlsx MsForums.xlsx (8.7 KB, 9 views)
Reply With Quote
  #2  
Old 01-05-2021, 03:00 PM
Purfleet Purfleet is offline Mix COUNTIF and SUMPRODUCT? Windows 10 Mix COUNTIF and SUMPRODUCT? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

I think i got all your criteria on this one

=SUMPRODUCT(--(LEFT(I19:I23,3)="ABC")*(ISODD(MID(I19:I23,4,3)*(J 19:J23="Here"))))
Attached Files
File Type: xlsx Copy of MsForums_Purfleet.xlsx (9.7 KB, 10 views)
Reply With Quote
  #3  
Old 01-07-2021, 01:15 AM
wk9128 wk9128 is offline Mix COUNTIF and SUMPRODUCT? Windows 10 Mix COUNTIF and SUMPRODUCT? Office 2007
Novice
 
Join Date: Dec 2020
Posts: 1
wk9128 is on a distinguished road
Default

Try , Array formula

=COUNT(0/N((LEFT(I19:I23,3)="ABC")*(MOD(ROW(I19:I23),2)=1)* (J19:J23="Here")))
Reply With Quote
  #4  
Old 01-10-2021, 02:47 AM
SoMany SoMany is offline Mix COUNTIF and SUMPRODUCT? Windows 7 64bit Mix COUNTIF and SUMPRODUCT? Office 2016
Advanced Beginner
Mix COUNTIF and SUMPRODUCT?
 
Join Date: Oct 2016
Posts: 51
SoMany is on a distinguished road
Default

Both work, thanks all.

I changed the first equation a little, in my head it makes more since to change the parenthesis. If this can run into issues, please let me know. Otherwise, problem solved.

=SUMPRODUCT(--(LEFT(I18:I28,3)="ABC")*(ISODD(MID(I18:I28,4,3)))* (J18:J28="Here"))
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct arndts Excel 6 01-09-2019 01:10 AM
Issue with SumProduct Perceptus Excel 1 03-13-2015 06:23 AM
Mix COUNTIF and SUMPRODUCT? Countifs and Sumproduct Algo Excel 6 11-13-2012 07:44 AM
sumproduct?? jer Excel 9 10-14-2012 10:00 AM
Mix COUNTIF and SUMPRODUCT? Sumproduct angie.chang Excel 3 06-14-2012 10:00 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:05 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft